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

• am-883928

  SSC Enthusiast

  Points: 116

  More actions

  July 31, 2009 at 3:54 am

  #134349

  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)

  }

• Jack Corbett

  SSC Guru

  Points: 184394

  More actions

  July 31, 2009 at 8:21 am

  #1032961

  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
  Consultant - Straight Path Solutions
  Check out these links on how to get faster and more accurate answers:
  Forum Etiquette: How to post data/code on a forum to get the best help
  Need an Answer? Actually, No ... You Need a Question

• Jeff Moden

  SSC Guru

  Points: 1004600

  More actions

  July 31, 2009 at 7:23 pm

  #1033229

  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)