List:General Discussion« Previous MessageNext Message »
From:SGreen Date:November 7 2005 6:43pm
Subject:Re: Stored Procedure Question
View as plain text  
"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




Thread
Stored Procedure QuestionJesse Castleberry7 Nov
  • Re: Stored Procedure QuestionSGreen7 Nov
  • Re: Stored Procedure QuestionPeter Brawley7 Nov