We help IT Professionals succeed at work.

mySQL vs. MS SQL - CREATE PROCEDURE differences?

I work primarily with MS SQL.  When I want to create a procedure I take an existing procedure and change it.  I make the changes to the procedure as I want and then change ALTER to CREATE and execute it.

Apparently it doesn't work the same way.  The '@' generates an error if CREATE syntax is used as opposed to ALTER.

I can't find a sample CREATE PROCEDURE with variables being passed.
Comment
Watch Question

Author

Commented:
Simple sample of what I'm trying to do
DELIMITER $$

CREATE PROCEDURE `myDatabase`.`SPU_UpdateContact_Web` 
(@ContactID bigint,
@ContactIndex varchar(100),
@Address varchar (100),
@City varchar (100),
@State varchar (100),
@ZipCode varchar(10)
BEGIN
UPDATE contact SET contact_index = @ContactIndex, address1 = @Address, city = @City, state = @State, zip_code = @ZipCode;
END

Open in new window

Author

Commented:
Forgot the ')'...close paren at the end of @ZipCode varchar(10)
Top Expert 2012
Commented:
http://dev.mysql.com/doc/refman/5.1/en/create-procedure.html

DELIMITER $$

CREATE PROCEDURE `myDatabase`.`SPU_UpdateContact_Web`
(ContactID bigint,
ContactIndex varchar(100),
Address varchar (100),
City varchar (100),
State varchar (100),
ZipCode varchar(10))
BEGIN
  UPDATE contact SET contact_index = ContactIndex, address1 = Address, city = City, state = State, zip_code = ZipCode;
  COMMIT;
END;
$$

Note: untested.
Top Expert 2012

Commented:

Author

Commented:
I see that @ is not included in MySQL.  Strangely, when I was attempting to execute this code in MySQL Workbench I would have sworn I removed the @ to no avail.

Also I was reviewing the dev.mysql.com.... link you posted and missed the SINGLE out param1 at the end of the CREATE line.

Will test.  Thanks much.

Author

Commented:
Excellent.  Worked great.

Explore More ContentExplore courses, solutions, and other research materials related to this topic.