MySQL Forums
Forum List  »  Stored Procedures

Re: about getting the auto increment to work in stored procedure
Posted by: Peter Brawley
Date: August 18, 2016 03:10PM

> set UserID = AUTO_INCREMENT;

That's not valid syntax. Look up last_insert_id() in the manual.

> IN FirstName varchar(10), etc ...

If your params have the same names as columns in the table you're working with, how is MySQL supposed to know when sproc code is referring to a param and when to a column? Adopt a param-naming convention that will prevent such confusions (eg, prefix each param name with p_) and stick to it.

But more fundamentally, what purpose is served by putting this Insert in a stored procedure? Why not just do the Insert then canvass last_insert_id() to get the auto_increment value that MySQL assigned to the inserted row?

If there really is a good reason requiring this sproc, read about using Out sproc params. You need to provide a MySQL variable into which the sproc can deposit the desired value, eg ...

set @newid=null;
call myproc( @newid, ... );
select @newid;

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: about getting the auto increment to work in stored procedure
368
August 18, 2016 03:10PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.