Replace @@identity with SCOPE_IDENTITY() in all stored procedures

  • I have about 400 stored procedures. Most of them have @@identity statement. How to replace @@identity with SCOPE_IDENTITY() in all stored procedures. I mean how to create small script that will replace @@identity with SCOPE_IDENTITY(). Something like the following pseudo-code:

    procedures[] = getAllStoredProcedures()

    foreach (sp in procedures)

    {

    var new_sp =GetStoredProcedureText(sp).Replace(@@identity,SCOPE_IDENTITY())

    UpdateStoredProcedure(sp, new_sp)

    }

  • While you will be better off with SCOPE_IDENTITY() in place of @@Identity you need to be aware that there is a bug in SCOPE_IDENTITY(). See this post.

    With 2005 the best way to get that information is probably the OUTPUT clause.

    I'd start the process by querying sys.sql_modules to get the stored procedure text, you'll need to change the CREATE to ALTER in addition to changing the reference to @@IDENTITY.

  • Jack Corbett (7/31/2009)


    While you will be better off with SCOPE_IDENTITY() in place of @@Identity you need to be aware that there is a bug in SCOPE_IDENTITY(). See this post.

    With 2005 the best way to get that information is probably the OUTPUT clause.

    I'd start the process by querying sys.sql_modules to get the stored procedure text, you'll need to change the CREATE to ALTER in addition to changing the reference to @@IDENTITY.

    Aaron's post leads to a CONNECT post where they talk about the problem... where's the code that was submitted with the problem?

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply