Retrieve IDENTITY Column Values with @@identity

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