asked on

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.

TSFLLC

ASKER

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

TSFLLC

ASKER

Forgot the ')'...close paren at the end of @ZipCode varchar(10)

ASKER CERTIFIED SOLUTION

johanntagle

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

johanntagle

TSFLLC

ASKER

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.

TSFLLC

ASKER

Excellent. Worked great.