Instance for SQL Server to obtain the returned values of Stored Procedures

1. OUPUT parameter Return Value

Copy codeThe Code is as follows:
Create procedure [dbo]. [nb_order_insert] (
@ O_buyerid int,
@ O_id bigint OUTPUT
)
AS
BEGIN
Set nocount on;
BEGIN
Insert into [Order] (o_buyerid)
VALUES (@ o_buyerid)
SET @ o_id = @ IDENTITY
END
END

How to obtain data in the stored procedure:

Copy codeThe Code is as follows:
DECLARE @ o_buyerid int
DECLARE @ o_id bigint
EXEC [nb_order_insert] @ o_buyerid, @ o_id output

2. RETURN Value

Copy codeThe Code is as follows:
Create procedure [dbo]. [nb_order_insert] (
@ O_buyerid int,
@ O_id bigint OUTPUT
)
AS
BEGIN
Set nocount on;
IF (EXISTS (SELECT * FROM [Shop] WHERE [s_id] = @ o_buyerid ))
BEGIN
Insert into [Order] (o_buyerid) VALUES (@ o_buyerid)
SET @ o_id = @ IDENTITY
RETURN 1-insert Success 1
END
ELSE
RETURN 0-insert failure RETURN 0 END

How to obtain data in a stored procedure

Copy codeThe Code is as follows:
DECLARE @ o_buyerid int
DECLARE @ o_id bigint
DECLARE @ result bit
EXEC @ result = [nb_order_insert] @ o_buyerid, o_id output

3. SELECT dataset Return Value

Copy codeThe Code is as follows:
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 obtain data in a stored procedure

(1) Use a temporary table

Copy codeThe Code is as follows:
Create table [dbo]. [Temp] (
[O_id] [bigint] IDENTITY (1, 1) not for replication not null,
[O_buyerid] [int] NOT NULL
)
INSERT [Temp] EXEC [nb_order_select] @ o_id
-In this case, Temp is the result set after EXEC executes the SELECT statement.
SELECT * FROM [Temp]
DROP [Temp]-delete a temporary table

1. Get Return Value

Copy codeThe Code is as follows:
SqlConnection conn = new SqlConnection (ConfigurationManager. ConnectionStrings ["ConnStr"]. ToString ());
Conn. Open ();
SqlCommand MyCommand = new SqlCommand ("nb_order", conn); // name of the stored procedure
MyCommand. CommandType = CommandType. StoredProcedure; // specify the type as stored procedure
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 (); // executes the Stored Procedure
Response. Write (MyCommand. Parameters ["@ return"]. Value. ToString (); // get the return Value

2. Get Output parameter values

Copy codeThe Code is as follows:
SqlConnection conn = new SqlConnection (ConfigurationManager. ConnectionStrings ["ConnStr"]. ToString ());
Conn. Open ();
SqlCommand MyCommand = new SqlCommand ("nb_order", 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 (); // specify the return Value of the stored procedure

C # receive Stored Procedure return values:

Copy codeThe Code is as follows:
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; // you can specify the name and value of the stored procedure AddWithValue. You must specify the name, type, and value for Add.
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; // specify that the output parameter is the return value.
Try
{
Conn. Open ();
Cmd. ExecuteNonQuery (); // execute the Stored Procedure
IRet = (int) cmd. Parameters ["@ RETURN_VALUE"]. Value; // gets the return Value.
}
Catch (SqlException ex)
{
Throw ex;
}
Finally
{
Conn. Close ();
}
Return iRet;
}

C # receiving the output parameters of the stored procedure:

Copy codeThe Code is as follows:
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; // use the Add method to Add names, types, and Output Parameters.
Try
{
Conn. Open ();
Cmd. ExecuteNonQuery ();
IRet = (decimal) cmd. Parameters ["@ Amount"]. Value; // gets the output Parameters in the stored procedure.
}
Catch (SqlException ex)
{
Throw ex;
}
Finally
{
Conn. Close ();
}
Return iRet;
}

C # result set:

Copy codeThe Code is as follows:
String sqlw = string. Format ("exec sp_UserInfo {0}", uid );
DataTable dsuser = SqlConn. GetDataSet (sqlw). Tables [0];

Public static DataSet GetDataSet (string SQL)
{
String connStr = System. Configuration. ConfigurationManager. ConnectionStrings ["ConnStr"]. ToString ();
SqlConnection conn = new SqlConnection (connStr );
SqlCommand cmd = new SqlCommand (SQL, conn );
SqlDataAdapter da = new SqlDataAdapter (cmd); // use SqlDataAdapter to retrieve the result set and put it into dataset.
DataSet ds = new DataSet ();
Da. Fill (ds );
Conn. Close ();
Cmd. Dispose ();
Return ds;
}