1.OUPUT parameter return value
Copy Code code 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
Methods to obtain in stored procedures:
Copy Code code as follows:
DECLARE @o_buyerid int
DECLARE @o_id bigint
EXEC [Nb_order_insert] @o_buyerid, @o_id output
2.RETURN Procedure return value
Copy Code code 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 successfully returned 1
End
ELSE
Return 0-insert failure returns 0 end
Methods of obtaining in stored procedures
Copy Code code 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 Data Set return value
Copy Code code 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
Methods of obtaining in stored procedures
(1) Methods of using temporary tables
Copy Code code as follows:
CREATE TABLE [dbo]. [Temp] (
[O_ID] [bigint] IDENTITY (1,1) not to REPLICATION not NULL,
[O_buyerid] [INT] Not NULL
)
INSERT [Temp] EXEC [Nb_order_select] @o_id
– then Temp is the result set after the select is executed by exec
SELECT * FROM [Temp]
drop [temp]-Delete temporary table
1. Get return returned value
Copy Code code as follows:
SqlConnection conn = new SqlConnection (configurationmanager.connectionstrings["ConnStr"). ToString ());
Conn. Open ();
SqlCommand mycommand = new SqlCommand ("Nb_order", conn); Stored Procedure Name
myCommand.CommandType = CommandType.StoredProcedure; Specifies that the type is 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 (); Executing stored procedures
Response.Write (mycommand.parameters["@return"]. Value.tostring ()); Get return value
2. Get output parameter value
Copy Code code 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 obtained stored procedure
C # receive stored procedure return value:
Copy Code code 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; Specifies a stored procedure addwithvalue can specify a name and value, and add needs to specify the name, type, and then give the value
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; Specifies that the output parameter is the return value
Try
{
Conn. Open ();
Cmd. ExecuteNonQuery (); Executing stored procedures
IRet = (int) cmd. parameters["@RETURN_VALUE"]. Value; Get the value of return
}
catch (SqlException ex)
{
Throw ex;
}
Finally
{
Conn. Close ();
}
return iRet;
}
C # receives output parameters for stored procedures:
Copy Code code 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; Add a name, type, and output parameter to it using the Add method
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 # Gets the result set:
Copy Code code 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); Take the result set out directly into the dataset using SqlDataAdapter
DataSet ds = new DataSet ();
Da. Fill (DS);
Conn. Close ();
Cmd. Dispose ();
return DS;
}
