Select SCOPE_IDENTITY () and SELECT @ IDENTITY usage

Select SCOPE_IDENTITY ()

Returns the value of the IDENTITY column in the last row of the data table in the preceding operation;

Returns the last IDENTITY value inserted into the IDENTITY column in the same scope. A scope is a module-stored procedure, trigger, function, or batch processing. Therefore, if the two statements are in the same stored procedure, function, or batch processing, they are in the same scope.

SELECT @ IDENTITY

Returns the value of the IDENTITY column in the last row of the last data table in the preceding operation;
Create a table:

Create table T_User (F_ID int identity (1, 1) not null, F_Name varchar (20) not null)

Insert data:

Insert into T_User (F_Name) values ('who I am ') Select SCOPE_IDENTITY ()

Stored Procedure:

Create procedure [dbo]. [sp_User] (@ F_Name int)
Begin tran Insertinto_T_User
Insert into dbo. T_User (F_Name) values (@ F_Name)
Select SCOPE_IDENTITY ()