Obtain the output parameter and auto-incrementing column @ identity variable of the SQL stored procedure in c #.

SQL output usage

C # Reading 269 Comment 2 Font size: large, medium, small

I have never found a good method to reference stored procedures with returned values, which has led me to a lot of detours in adding data. Recently, after reading a lot of information, finally, a good method was found in an instance of Microsoft.
First, write a stored procedure with a returned value.
Create procedure proc_name
@ Para1 nchar (20), -- input parameter
@ Para2 int = null out -- output parameter for the program
As
Set nocount on
If (not exists (select * from employee where em_name = @ para1 ))
Begin
Insert into employee (name) values (@ para1)
Select @ para2 = @ identity -- returns the ID of the added record.
Return 1 -- return whether data is successfully added
End
Else
Return 0 -- return failed
Go
Then the method for calling the Stored Procedure
Sqlcommand command;
Command = new sqlcommand (proc_name, new sqlconnection (connectionstr ));
Command. paraments. add ("@ para1"), "name1"); // enter the parameter, employee name
Command. paraments. add (new sqlparament ("@ para2", // generate an output parameter
SqlDbType. Int; // parameter data type
ParamenterDirection. OutPut, // input/OutPut type
0,
0,
String. Emplty,
DataRowVerstion. Default,
Null) // parameter value.
);
Command. commandtype = commandtype. StoredProcedure;
Command. connection. open ();
Command.exe cutenonQuery ();
Int pkid = (int) command. Parameters ["@ para2"]. value; // obtain the value of the output parameter.
Command. connection. close ();
Here is the reference output parameter. If you want to reference the return value (whether data is successfully added), you only need to change the ParamenterDirection type to returnvalue. You can change the parameter name by yourself.

 

 

SQL Server 2005 returns the id of the inserted Data Entry

@ Identity system variable

 

 

From http://hi.baidu.com/xiaoxiaolq/blog/item/b290410fc47c54206159f308.html