Gets the stored procedure return value and gets the return value in the code

Gets the stored procedure return value and gets the return value in the code1.OUPUT Parameter return value
example: Inserting a record into the order table, returning its identity

CREATE PROCEDURE [dbo]. [Nb_order_insert](
@o_buyerid int ,
@o_id bigintOUTPUT
)
as
BEGIN
SET NOCOUNT on;
BEGIN
INSERT into [Order](O_buyerid)
VALUES (@o_buyerid )
SET @o_id = @ @IDENTITY
END
END
method of obtaining in stored procedure:

DECLARE @o_buyerid int
DECLARE @o_id bigint
EXEC [Nb_order_insert] @o_buyerid, o_id bigint
2.RETURN Procedure return value

CREATE PROCEDURE [dbo]. [Nb_order_insert](
@o_buyerid int ,
@o_id bigintOUTPUT
)
as
BEGIN
SET NOCOUNT on;
IF (EXISTS (SELECT * from [shop] WHERE [s_id] = @o_shopid))
BEGIN
INSERT into [Order](O_buyerid)
VALUES (@o_buyerid )
SET @o_id = @ @IDENTITY
RETURN 1-Insert Successful return 1
END
ELSE
RETURN 0-Insert failed to return 0
END
How to get in a stored procedure
DECLARE @o_buyerid int
DECLARE @o_id bigint
DECLARE @result Bit
EXEC @result = [Nb_order_insert] @o_buyerid, o_id bigint
3.SELECT Data Set return value

CREATE PROCEDURE [dbo]. [Nb_order_select](
@o_id int
)
as
BEGIN
SET NOCOUNT on;
SELECT O_id,o_buyerid from [Order]
WHERE o_id = @o_id
GO
How to get in a stored procedure
(1), the method of using temporary table

CREATE TABLE [dbo]. [Temp] (
[O_ID] [bigint] The IDENTITY (REPLICATION) not is NULL for
[O_buyerid] [INT] Not NULL
)
INSERT [Temp] EXEC [Nb_order_select] @o_id
– Temp is the result set after exec executes select
SELECT * FROM [Temp]
drop [temp]-Delete temp table
(2), the speed is not good. (not recommended)

SELECT * FROM OPENROWSET (' provider_name ', ' trusted_connection=yes ', ' exec nb_order_select ')

1. Get return value

program code//Stored procedure
Create PROCEDURE MYSQL
@a int,
@b int
As
return @a + @b
GO
SqlConnection conn = new SqlConnection (configurationmanager.connectionstrings["LocalSqlServer"]. ToString ());
Conn. Open ();
SqlCommand mycommand = new SqlCommand ("MYSQL", conn);
myCommand.CommandType = CommandType.StoredProcedure;
MYCOMMAND.PARAMETERS.ADD (New SqlParameter ("@a", SqlDbType.Int));
mycommand.parameters["@a"]. Value = 10;
MYCOMMAND.PARAMETERS.ADD (New SqlParameter ("@b", SqlDbType.Int));
mycommand.parameters["@b"]. Value = 20;
MYCOMMAND.PARAMETERS.ADD (New SqlParameter ("@return", SqlDbType.Int));
mycommand.parameters["@return"]. Direction = ParameterDirection.ReturnValue;
Mycommand.executenonquery ();
Response.Write (mycommand.parameters["@return"). Value.tostring ());

2. Obtain output parameter values

program code//Stored procedure
Create PROCEDURE MYSQL
@a int,
@b int,
@c int Output
As
Set @c = @a + @b
GO
SqlConnection conn = new SqlConnection (configurationmanager.connectionstrings["LocalSqlServer"]. ToString ());
Conn. Open ();
SqlCommand mycommand = new SqlCommand ("MYSQL", conn);
myCommand.CommandType = CommandType.StoredProcedure;
MYCOMMAND.PARAMETERS.ADD (New SqlParameter ("@a", SqlDbType.Int));
mycommand.parameters["@a"]. Value = 20;
MYCOMMAND.PARAMETERS.ADD (New SqlParameter ("@b", SqlDbType.Int));
mycommand.parameters["@b"]. Value = 20;
MYCOMMAND.PARAMETERS.ADD (New SqlParameter ("@c", SqlDbType.Int));
mycommand.parameters["@c"]. Direction = ParameterDirection.Output;
Mycommand.executenonquery ();
Response.Write (mycommand.parameters["@c"). Value.tostring ());

C # receives the stored procedure return value :

public static int User_add (User us)
{
int iRet;
SqlConnection conn = new SqlConnection (CONN_STR);
SqlCommand cmd = new SqlCommand ("User_add", conn);
Cmd.commandtype = CommandType.StoredProcedure;
Cmd. Parameters.addwithvalue ("@UName", US. UName);
Cmd. Parameters.addwithvalue ("@UPass", US. UPass);
Cmd. Parameters.addwithvalue ("@PassQuestion", US. Passquestion);
Cmd. Parameters.addwithvalue ("@PassKey", US. passkey);
Cmd. Parameters.addwithvalue ("@Email", US. Email);
Cmd. Parameters.addwithvalue ("@RName", US. Rname);
Cmd. Parameters.addwithvalue ("@Area", US. area);
Cmd. Parameters.addwithvalue ("@Address", US. Address);
Cmd. Parameters.addwithvalue ("@ZipCodes", US. ZipCodes);
Cmd. Parameters.addwithvalue ("@Phone", US. Phone);
Cmd. Parameters.addwithvalue ("@QQ", US. QQ);
cmd. Parameters.Add ("@RETURN_VALUE", ""). Direction = ParameterDirection.ReturnValue;
Try
{
Conn. Open ();
Cmd. ExecuteNonQuery ();
iRet = (int) cmd. parameters["@RETURN_VALUE"]. Value;
}
catch (SqlException ex)
{
Throw ex;
}
Finally
{
Conn. Close ();
}
return iRet;
}

C # receives stored procedure output parameters :

public static decimal cart_useramount (int UID)
{
Decimal IRet;
SqlConnection conn = new SqlConnection (CONN_STR);
SqlCommand cmd = new SqlCommand ("Cart_useramount", conn);
Cmd.commandtype = CommandType.StoredProcedure;
Cmd. Parameters.addwithvalue ("@UID", UID);
cmd. Parameters.Add ("@Amount", Sqldbtype.decimal). Direction=parameterdirection.output;
Try
{
Conn. Open ();
Cmd. ExecuteNonQuery ();
IRet = (decimal) cmd. parameters["@Amount"]. Value;
}
catch (SqlException ex)
{
Throw ex;
}
Finally
{
Conn. Close ();
}
return iRet;
}

Gets the stored procedure return value and gets the return value in the code