There is a table whose primary key is int and automatically increases. When I insert an entry into this table without providing a primary key, the database automatically adds a unique primary key.
The problem is:
How can I get the primary key value of the item just inserted?
1,
Stored Procedures return values in two ways:
1. output Parameters
Second, it is implemented through return.
An output parameter has been declared in your stored procedure. As long as you have assigned a value to this value in your stored procedure, it can return it.
We recommend that you use the output parameter, because it can return multiple, and return can only be one. return is generally used to return the affected number of rows, error codes, etc.
2,
To avoid incorrect database values for multiple users
Correct should be like this:
SELECT * FROM table WHERE (AutoIncreaseColumn = SCOPE_IDENTITY ())
3,
SCOPE_IDENTITY, IDENT_CURRENT, and @ IDENTITY, all return values inserted into the IDENTITY column.
IDENT_CURRENT returns the last generated id value for any session and specific tables in any scope. IDENT_CURRENT is not restricted by the scope and session, but by the specified table. IDENT_CURRENT returns the value generated for a specific table in any session and scope.
@ IDENTITY returns the last generated id value for any table in all scopes of the current session.
SCOPE_IDENTITY returns the last generated id value for the current session and any table in the current scope.
SCOPE_IDENTITY and @ IDENTITY return the last id value generated in any table in the current session. However, SCOPE_IDENTITY only returns the value inserted into the current scope; @ IDENTITY is not limited to a specific scope.
IDENT_CURRENT () always returns the id value inserted at the end of the specified table
@ IDENTITY: return the id value of the current session. No matter whether it is in the same scope or not, in test 1 and 2, you can see that it returns the id value of the records inserted in the trigger, in Test 3, NULL is returned because there is no insert record for the current session.
SCOPE_IDENTITY () returns the id value of the same scope of the current session. Therefore, in test 1 and 2, the returned value is not affected by the trigger. In Test 3, NULL is returned because there is no insert record in the current session.
4,
Select @ identity from youetable immediately after insertion
For a stored procedure, @ identity can be used as the returned value after insertion.
5,
Obtain the return value in the stored procedure in the query analyzer.
Declare @ return_value int
Set @ return_value = 1
Exec EXT_SUBJECTINSERT_11
"2006", "difficult", "High School ",
"Chinese", "Q & A", "knowledge points", "Phase I", "dd", "8:32:54", "zhongwen ",
"Insert", 1, 1,
"Search", "dd", "fir", @ return_value output
Select @ return_value
6,
DECLARE @ tmpCount int
SET @ tmpCount int = (select count (*) FROM table name WHERE the condition you want to search)
IF (@ tmpCount = 0)
BEGIN
INSERT operation
RETURN 1
END
ELSE
BEGIN
RETURN 0
END
Stored Procedures. You can refer to the preceding descriptions for writing stored procedures, but sqlparameter is required for program calling. You can find a bunch of methods for using it on the Internet.
Then execute sqlcommand. If the program only defines a return value parameter (returnvalue), it can be determined by this parameter after the execution (excutenoquery, if an output parameter is defined in the database and program, it can be determined by the value of this parameter after execution.
7,
Use int returnValue = Convert. ToInt32 (command. ExecuteScalar (); execute the SQL statement to obtain the returned value
8,
Cmd. Parameters ["return_value"]. Direction = ParameterDirection. ReturnValue;
This article is reproduced