July 31, 2009 at 3:54 am
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)
}
July 31, 2009 at 8:21 am
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
July 31, 2009 at 7:23 pm
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
Change is inevitable... Change for the better is not.
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy