Results 1 to 7 of 7
  1. #1
    Join Date
    Nov 2004
    Posts
    3

    Unanswered: Help with a sql procedure

    Hi,

    I have a table that has one id column that is unique, primary key, identifier... And counts 1 every time data is inserted into it!

    Now I want to create a procedure that takes care of inserting data into the table and some other tables that are related to this table.
    Of course, then I want to retreive the ID from the first column from the INSERT.. how do I do it ?

  2. #2
    Join Date
    Aug 2004
    Posts
    330
    Use the parameter IDENTITY_VAL_LOCAL.

  3. #3
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    What OS and DB2 version are you using?

    If UDB V7.2 or greater, then look into Identity_Val_Local().

    Andy

  4. #4
    Join Date
    Nov 2004
    Posts
    3
    Hi guys,

    thanks for your quick response!

    I use Windows XP and the latest version of DB2 (8,2?!) which I downloaded from the partner site at ibm.com a couple of days ago.

    I will look into IDENTITY_VAL_LOCAL()

    Can this variable retreive the value of the row that I just inserted?

  5. #5
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    That is its sole purpose in life. Just be sure to use it right after the insert and before any commit.

    Andy

  6. #6
    Join Date
    Nov 2004
    Posts
    3
    Quote Originally Posted by ARWinner
    That is its sole purpose in life. Just be sure to use it right after the insert and before any commit.

    Andy
    Thanks man, you're the hero of the day!

    Another question: Is the IDENTITY_VAL_LOCAL() always returning the id of the row that i inserted in my query? Or is it possible that I insert a row, and another person inserts a row a millisecond after and when I use the IDENTITY_VAL_LOCAL() I get the id that was created for the user that created the 2:nd row?

  7. #7
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    It works only within the session it was invoked in. Only the value created in the session it is called in will be returned. Read the manual, it explains all this and more.

    Andy

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •