Results 1 to 5 of 5
  1. #1
    Join Date
    Sep 2003
    Location
    Los Angeles
    Posts
    103

    Unanswered: Return the lastest [Identity] field value

    Hi!

    I have a question that might sound obvious to some of you but (obviously) not to me.

    I have a stored procedure that adds a new record to a table that has an [Identity] field (TableID).

    I want to return the value of the TableID of the newly created record. Sound simple ?

    Thanks.

  2. #2
    Join Date
    Dec 2002
    Posts
    1,245
    Try @@IDENTITY

    Note that this will return the IDENTITY value for the table which was most recently updated. Thus, if you have a stored proc that inserts a record into tblHeader and a record into tblDetail and then you return @@IDENTITY, then @@IDENTITY will be the IDENTITY value for tblDetail.

    To get around this limitation, you can use another function called IDENT_CURRENT('table_name').

    See the SQL BOL for additional documentation.

    regards,

    hmscott

  3. #3
    Join Date
    Sep 2003
    Location
    Los Angeles
    Posts
    103
    Originally posted by hmscott
    Try @@IDENTITY

    Note that this will return the IDENTITY value for the table which was most recently updated. Thus, if you have a stored proc that inserts a record into tblHeader and a record into tblDetail and then you return @@IDENTITY, then @@IDENTITY will be the IDENTITY value for tblDetail.

    To get around this limitation, you can use another function called IDENT_CURRENT('table_name').

    See the SQL BOL for additional documentation.

    regards,

    hmscott
    What about Race Condition ? .. what if another user inserts another record just after I added my record and before I read the IDENT_CURRENT(tableName) ?
    I would get the wrong value !? It would be the latest identity but it won't be the one I am looking for.

  4. #4
    Join Date
    Dec 2002
    Posts
    1,245
    What's the usage on this database? If the insert and the call to IDENT_CURRENT are in the same SP, then the likelihood that another user can get in and insert a new record is pretty slim.

    Look at SQL BOL, check the limitations for each @@IDENTITY, IDENT_CURRENT and SCOPE_IDENTITY and determine which one most closely meets your needs.

    Alternatively, don't use IDENTITY, use a shadow GUID column instead.

    Regards,

    hmscott

  5. #5
    Join Date
    Sep 2003
    Location
    Los Angeles
    Posts
    103
    Originally posted by hmscott
    If the insert and the call to IDENT_CURRENT are in the same SP, then the likelihood that another user can get in and insert a new record is pretty slim.
    The [insert] and the call IDENT_CURRENT are indeed in the same SP. But that doesn't garantee that there won't be a race condition, does it ?

    I checked BOL and IDENT_CURRENT would work fine .. but I want to be sure 100% that I read the right value.

    Even a "slim" chance that an another user can get in and insert a record is is not good enough in my case.

    It sounds like I have to do some kind of explicit "locking":
    [LOCK]/[Insert]/[Read latest indentity]/[Unlock].

    Any suggestion ?

Posting Permissions

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