0

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

2
Contributors
2
Replies
3
Views
6 Years
Discussion Span
Last Post by ProfessorPC
1

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

Edited by urtrivedi: n/a

This question has already been answered. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.