"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
> so far with it, but it's complaining about the INSERT command. It's a
> 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
> 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
> INSERT INTO Campers (FirstName, LastName, UserName, Password) VALUES
> (cFirstName, cLastName, cUserName, cPassword) // ERROR RIGHT HERE.
> AddedID = LAST_INSERT_ID()
> It complains about the INSERT INTO command where I've indicated above,
> a Syntac error. What is the proper syntax for this?
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:
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.
Unimin Corporation - Spruce Pine