MySQL Forums :: Stored Procedures :: about getting the auto increment to work in stored procedure


Advanced Search

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
about getting the auto increment to work in stored procedure 2550 Bob Smith 08/18/2016 02:40PM
Re: about getting the auto increment to work in stored procedure 322 Peter Brawley 08/18/2016 03:10PM
Re: about getting the auto increment to work in stored procedure 280 Bob Smith 08/19/2016 11:48AM
Re: about getting the auto increment to work in stored procedure 280 Peter Brawley 08/19/2016 12:26PM


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.