If possible you should use SCOPE_IDENTITY() for the reasons stated - as noted direct from BOL.
-----Original Message----- From: Rich Kroll [mailto:[EMAIL PROTECTED] Sent: 04 April 2006 15:31 To: CF-Talk Subject: RE: cftransaction In MS SQL I've seen two ways to handle returning the last inserted primary key: INSERT INTO t_doctors (#ColNames#) VALUES (#preserveSingleQuotes(ColValues)#); SELECT SCOPE_IDENTITY() AS newpkey; This will return the last inserted primary key for the current scope (session), so there is no concern of race conditions. INSERT INTO t_doctors (#ColNames#) VALUES (#preserveSingleQuotes(ColValues)#); SELECT @@identity AS newpkey; While this will return the last inserted primary key across scopes (sessions). Using @@identity will leave you open to potential problems with race conditions. This is from information I've read in the BOL as well as been informed from our DBA. I can say that when using SCOPE_IDENTITY() I've never found any errors under load, but if anyone else has any input I'd love to hear it. Rich Kroll Application Developer ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:236878 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
