Use the @@identity global variable to retrieve the last value inserted into an IDENTITY column. The value of @@identity changes each time an insert or select into attempts to insert a row into a table. @@identity does not revert to its previous value if the insert or select into statement fails, or if the transaction that contains it is rolled back. If the statement affects a table without an IDENTITY column, @@identity is set to 0.
If the statement inserts multiple rows, @@identity reflects the last value inserted into the IDENTITY column.
The value for @@identity within a stored procedure or trigger does not affect the value outside the stored procedure or trigger. For example:
select @@identity
--------------------------------------- 101
create procedure reset_id as set identity_insert sales_daily on insert into sales_daily (syb_identity, stor_id) values (102, "1349") select @@identity select @@identity execute reset_id
--------------------------------------- 102
select @@identity
--------------------------------------- 101
