DevPinoy.org
A Filipino Developers Community
jokiz » Revisiting @@IDENTITY AND SCOPE_IDENTITY()

>>> First two to make 3 wins! <<<

Revisiting @@IDENTITY AND SCOPE_IDENTITY()
I previously posted my views regarding @@IDENTITY variable and SCOPE_IDENTITY() functions in sql server here and luckily with the single stored procedure in our project using NHibernate, i was able to revisit some of its definitions.  

My teammate whom i asked help from in developing the said stored procedure (since i really am not good to using cursors), used @@IDENTITY to obtain the generated incremental identity to a table.  I informed him of the consequences of the said usage and revised the stored procedure using SCOPE_IDENTITY().  The stored procedure returns the said identity generated from the insert and this morning, the row was inserted but the stored procedure was returning a System.DbNull.Value.  

Turns out that the query used to insert in the table is the dynamic one executed using sp_executesql and the storing of SCOPE_IDENTITY is done after the said execution which is of different scope already.
Posted 11-15-2006 9:55 AM by jokiz
Filed under: ,

Comments

bonskijr wrote re: Revisiting @@IDENTITY AND SCOPE_IDENTITY()
on 11-15-2006 5:23 AM

what was the solution then? include SCOPE_IDENTITY() on the dynamic sql?

btw:you're using NHibernate 1.2, I presume because of stored proc support

jokiz wrote re: Revisiting @@IDENTITY AND SCOPE_IDENTITY()
on 11-15-2006 6:40 PM

yep, include it in the dynamic sql, we're still not using 1.2 (still in beta) since we are approaching a release, maybe when we start the next phase

Ron Van Dalen wrote Ron Van Dalen
on 02-10-2008 10:18 PM

It is necessary that you find the greatest web sites.


Copyright DevPinoy 2005-2008