On Wed, Apr 28, 2010 at 6:19 PM, <[email protected]> wrote: > Yes, I would agree that is a much simpler and safer alternative. since > scope_identity() is already specific to your connection you don't have > to worry about locking and such altogether. > > ~Brad > > -------- Original Message -------- > Subject: Re: SQL Gurus... obtaining the correct Incremental ID > From: Leigh _ <[email protected]> > Date: Wed, April 28, 2010 4:44 pm > To: cf-talk <[email protected]> > > > >That would ONLY work if you used SET TRANSACTION ISOLATION LEVEL > >SERIALIZABLE or WITH(TABLOCKX HOLDLOCK) > > > >Even with with rowlock, another spid can insert additional records into > >the table and the select max() might return a bogus value. > > Aside from being unsafe without the right isolation level, it is not > necessary in MSSQL 2005 or CF8+. As use mentioned use SCOPE_IDENTITY() > or cfquery's result attribute. >
YO! 2005 + USE OUTPUT Duh! http://msdn.microsoft.com/en-us/library/ms177564.aspx OUTPUT Clause (Transact-SQL) Returns information from, or expressions based on, each row affected by an INSERT, UPDATE, DELETE, or MERGE statement. These results can be returned to the processing application for use in such things as confirmation messages, archiving, and other such application requirements. The results can also be inserted into a table or table variable. Additionally, you can capture the results of an OUTPUT clause in a nested INSERT, UPDATE, DELETE, or MERGE statement, and insert those results into a target table or view. ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:333211 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
