January 4th, 2013, 06:58 PM
Auto_inc field and stored procedure
I have a stored procedure which adds a record to a table with an auto_inc field if the passed in ID is 0 (and updates it if it is > 0). The insert works correctly; the auto_inc field populates correctly (automatically).
The problem is that I would like to return the ID value (generated in the auto_inc field). I've tried several ways, including LAST_INSERT_ID(), but it does not get the value. Once the stored procedure completes, I can get the value, but I cannot while execution is still within the stored procedure.
I assume this is some latency or data commit problem, perhaps a setting or other command/code I need to do? Or is this working "as designed", and I need to shell out to a sub stored procedure to make this work the way I want?
January 4th, 2013, 07:42 PM
perhaps you could show the code where you tried to use LAST_INSERT_ID in the procedure?
January 5th, 2013, 07:52 AM
CREATE PROCEDURE `sp_SaveComment`(IN nCommentID int, IN nUserID INT, IN dCreateDate datetime, IN sContent text)
if nCommentID = 0 then
Insert into comments(UserID, CommentDate, Content) Values(nUserID, dCreateDate, sContent);
/* NOTE: CommentID is an AUTO_INC field. Following line does not get the new CommentID value
Select nCommentID = LAST_INSERT_ID(); /*CommentID from comments order by CommentID Desc Limit 1;*/
insert into KeywordProcess(CommentID, Action) Values (nCommentID, 'A');
Update comments set Content = sContent Where CommentID = nCommentID;
insert into KeywordProcess(CommentID, Action) Values (nCommentID, 'U');
select nCommentID as 'CommentID';
January 5th, 2013, 02:34 PM
it's been a long time since i worked with stored procs, but i think the following is wrong --
try this instead --
Select nCommentID = LAST_INSERT_ID();
Select LAST_INSERT_ID() INTO nCommentID
January 5th, 2013, 03:51 PM