why we use SELECT @@IDENTITY at last in Stored procedure sql

Question

Most of the Stored procedure having SELECT @@IDENTITY syntax at last why that used?

Aakash Maurya · Answer

Normally every table has a primary key created. And Primary key is mostly auto incremented values. And SELECT @@IDENTITY returns the last inserted auto incremented ID. This is used to return the last inserted auto generated ID that can be used to enter data in any next table. You can get brief documentation here https://docs.microsoft.com/en-us/sql/t-sql/functions/identity-transact-sql?view=sql-server-2017

Code Alone · Answer

Hi Mani, Your question is right. Most of stored procedures have @@IDENTITY . We use this to fetch that last identity column value. (Identity column is a column which we set to auto increament. Mostly we make it primary, sometimes not. But without declaration it can we used as Primary Key. Duplicate record never inserted there ) When we insert a record in table, then we don't know that which record inserted there. After the record insertion @@IDENTITY provide us that record key, which stored in identity column. You can refer http://csharp-video-tutorials.blogspot.com/2012/08/identity-column-in-sql-server-part-7.html or for video tutorial https://www.youtube.com/watch?v=aOkFE6NLGCQ

Jignesh Kumar · Answer

@@IDENTITY is used for return last value inserted for identity coulmn from table regadless for SQL statement scope