I am having an issue creating a stored procedure but I keep getting an error
"Error Code : 1064
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'CREATE PROCEDURE sp_Insert_Address
(
in p_UserID int(11),
in p_Type VARCHAR' at line 3"


Below is my SP

DROP PROCEDURE IF EXISTS sp_Insert_Address

CREATE PROCEDURE sp_Insert_Address
(
	IN p_UserID INT(11),
	IN p_Type VARCHAR(50),
	IN p_Address VARCHAR(50),
	IN p_City VARCHAR(75),
	IN p_State VARCHAR(2),
	IN p_Zip VARCHAR(15),
	IN p_mDefault INT(1)
)
BEGIN
    INSERT INTO addresses 
	(
		UserID, 
		TYPE, 
		Address, 
		City, 
		State, 
		Zip, 
		mDefault
	) 
	VALUES 
	(
		p_UserID, 
		p_Type, 
		p_Address, 
		p_City, 
		p_State, 
		p_Zip, 
		p_mDefault
	);
END

Any help is appreciated

Recommended Answers

All 2 Replies

put need to put deleimiter between sql commands like semicolon or any other.

DROP PROCEDURE IF EXISTS sp_Insert_Address;

if you are runing this commands in phpmyadmin, then you can use another command terminator like @@

so you code will look like

DROP PROCEDURE IF EXISTS sp_Insert_Address @@

CREATE PROCEDURE sp_Insert_Address
(
	IN p_UserID INT(11),
	IN p_Type VARCHAR(50),
	IN p_Address VARCHAR(50),
	IN p_City VARCHAR(75),
	IN p_State VARCHAR(2),
	IN p_Zip VARCHAR(15),
	IN p_mDefault INT(1)
)
BEGIN
    INSERT INTO addresses 
	(
		UserID, 
		TYPE, 
		Address, 
		City, 
		State, 
		Zip, 
		mDefault
	) 
	VALUES 
	(
		p_UserID, 
		p_Type, 
		p_Address, 
		p_City, 
		p_State, 
		p_Zip, 
		p_mDefault
	);
END@@

NOw when you copy above code in sql window, at the bottom you will find delimiter field there you replace semicolon (;) with double at sign (@@), and then execute statment

Perfect thanks

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.