"Jesse Castleberry" <JesseC@stripped> wrote on 11/07/2005 01:26:59 PM:
> I've got a stored procedure I'm trying to convert from MS SQL. I've
gotton
> so far with it, but it's complaining about the INSERT command. It's a
very
> simple stored procedure, so it should easy to figure out, but I'm not
> familiar with the MySQL Stored Procedure syntax. If someone can point
out
> what I'm doing wrong here, I'd appreciate it:
>
> CREATE Procedure sp_InsertNewCamper
> (
> in cFirstName NVarChar(30),
> in cLastName NVarChar(30),
> in cUserName NVarChar(30),
> in cPassword NVarChar(30),
> out AddedID Int
> )
> BEGIN
> INSERT INTO Campers (FirstName, LastName, UserName, Password) VALUES
> (cFirstName, cLastName, cUserName, cPassword) // ERROR RIGHT HERE.
> AddedID = LAST_INSERT_ID()
> END;
>
> It complains about the INSERT INTO command where I've indicated above,
with
> a Syntac error. What is the proper syntax for this?
>
> Thanks,
> Jesse
>
Within the stored procedures, you need to tell MySQL when you have reached
the end of each command. In order to do that, you have to use a
semicolon(;), just as you would while working interactively.
In order to define a stored procedure interactively you have to change the
CLI's command delimiter so that you can use a semicolon within the
definition of your stored procedure and not end the CREATE PROCEDURE
statement too early. Look at the interactive examples on this page:
http://dev.mysql.com/doc/refman/5.0/en/create-procedure.html
Can you see where they temporarily reset the command delimiter, created
their stored procedure (with each statement within the SP ending with a ;
), then reset the interactive command delimiter? The error message is
saying that you didn't end your INSERT statement... you forgot your
semicolon (;) to separate it from the statement where you tried to set the
value AddedID (that's going to be a different error, you neglected to use
SET or SELECT).
Don't give up, you almost got it.
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
