The SQL statement returns the primary key SCOPE_IDENTITY ()

Use after an SQL statement
SCOPE_IDENTITY ()

Of course, you can also use SELECT @ IDENTITY

However, the use of SELECT @ IDENTITY is globally up-to-date. incorrect values may be obtained.

Example:
Copy codeThe Code is as follows:
Insert into dbo. SNS_BlogData (userName) values ('jiangyun ');
SELECT SCOPE_IDENTITY ()

Gets the primary key return value for the insert into operation on the SQL-SERVER database, SCOPE_IDENTITY

After inserting a record, you want to immediately obtain the primary key return value in the data table. This primary key is automatically generated. In fact, there are many implementation methods, such as performing another query and obtaining it. Alternatively, you can obtain the maximum value before inserting data, and add one to the maximum value. There are many methods, but some are inconvenient.
In my opinion, the fastest way is to directly obtain the value of the primary key after inserting data, and then return it.
The method is as follows:
The SQL statement is as follows:
Insert into tableName (fieldname...) values (value...) SELECT @ identity as returnName;
Add SELECT @ identity as returnName to the SQL statement to obtain the value of the primary key.
Obtain the return value in the program:
Copy codeThe Code is as follows:
Public int sqlexecutereader (string SQL)
{
DBopen ();
SqlCommand myComm = new SqlCommand (SQL, Connection );
Int newID = Convert. ToInt32 (myComm. ExecuteScalar ());
DBclose ();
Return newID;
}

Of course, the primary key is automatically added for the int type here. The operations of DBopen (); DBclose (); are not mentioned here.

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 ()