Basic method of calling a stored procedure and using the return value
Author：Eve Cole
Update Time：2009-05-31 00:08:21
I have never found a good way to reference stored procedures with return values, which made me take a lot of detours in adding data. Recently, after consulting a lot of information, I finally found it in an instance of Microsoft. A good method.
First write a stored procedure with a return value
create procedure proc_name
@para1 nchar(20), --input parameters
@para2 int = null out --output parameters for program use
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 --returns whether the data was added successfully
end
else
return 0 --return failure
go
Then there is the method of calling the stored procedure
sqlcommand command;
command = new sqlcommand(proc_name,new sqlconnection(connectionstr));
command.paraments.add("@para1"),"name1"); //Input parameters, employee name
command.paraments.add(new sqlparament("@para2", //Generate an output parameter
SqlDbType.Int; //Parameter data type
ParamenterDirection.OutPut, //Input and output type
0,
0,
string.Emplty,
DataRowVerstion.Default,
null) //Parameter value, required when entering parameters
);
command.commandtype=commandtype.StoredProcedure;
command.connection.open();
command.executenonQuery();
int pkid=(int)command.Parameters["@para2"].value; //Get the value of the output parameter
command.connection.close();
Here are the reference output parameters. If you want to reference the return value (whether the data is added successfully), you only need to change the type of ParamenterDirection to returnvalue; then change the parameter name yourself.
