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.