#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2004
    Location
    Alexandria, VA
    Posts
    186
    Rep Power
    14

    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?

    Thanks...
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,026
    Rep Power
    4210
    no idea

    perhaps you could show the code where you tried to use LAST_INSERT_ID in the procedure?
    r937.com | rudy.ca
    please visit Simply SQL and buy my book
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2004
    Location
    Alexandria, VA
    Posts
    186
    Rep Power
    14
    Code:
    CREATE PROCEDURE `sp_SaveComment`(IN nCommentID int, IN nUserID INT, IN dCreateDate datetime, IN sContent text)
    BEGIN
    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');
    else
        Update comments set Content = sContent Where CommentID = nCommentID;
        insert into KeywordProcess(CommentID, Action) Values (nCommentID, 'U');
    end if;
    
    select nCommentID as 'CommentID';
    
    END
  6. #4
  7. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,026
    Rep Power
    4210
    it's been a long time since i worked with stored procs, but i think the following is wrong --
    Code:
    Select nCommentID = LAST_INSERT_ID();
    try this instead --
    Code:
    Select LAST_INSERT_ID() INTO nCommentID
    r937.com | rudy.ca
    please visit Simply SQL and buy my book
  8. #5
  9. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2004
    Location
    Alexandria, VA
    Posts
    186
    Rep Power
    14
    That was it! - thanks!

IMN logo majestic logo threadwatch logo seochat tools logo