Use the @@identity global variable to retrieve the last value inserted into an IDENTITY column. The value of @@identity changes each time an insert, select into, or bcp statement attempts to insert a row into a table. @@identity does not revert to its previous value if the insert, select into, or bcp 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
