"Jesse Castleberry" <[EMAIL PROTECTED]> 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
