The use of out,output,return in stored procedures

Parameters for the output type need to be added in the parameter list of the stored procedure. Parameters of the output type pass in the stored procedure and return their running values.

Parameters for the return value type do not need to be added in the parameter list of the stored procedure. The parameter of the return value type is the value of the last return of the stored procedure.

Output values and return values are used in the database:

-------------------------------------------------------

--Number of output parameters and return values obtained in the stored procedure

-------------------------------------------------------

CREATE PROCEDURE proc_test;1

@INPUT int,

@OUTPUT int OUTPUT

As

BEGIN

SET NOCOUNT on;

SELECT @OUTPUT = @INPUT

return @INPUT +1

End

Go

--Call output value and return value

DECLARE @OUT int, @RETURN int

EXEC @RETURN =proc_test;1

0,

@OUT output

SELECT [return value]= @RETURN, [Output value]= @OUT

return value Output value

----------- -----------

1 0

-----------------------------------------------------

--The output parameter acquisition in sp_executesql

-----------------------------------------------------

DECLARE @Para1 int, @Para2 int, @SUM int

EXECUTE sp_executesql

N ' SELECT @SUM = @Para1 + @Para2 ',

N ' @Para1 int, @Para2 int, @SUM int OUTPUT ',

5,5, @SUM OUTPUT

SELECT [Output value]= @SUM

Output value

-----------

10

======================================================================

The following stored procedures are lowered in. NET:

View plain Copy to clipboard print? <%@ page language= "C #"  autoeventwireup= "true"   codefile= "Default.aspx.cs"   inherits= "_default"  %>       <! doctype html public  "-//w3c//dtd xhtml 1.0 transitional//en"   "http:// Www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd ">      

 

View plain Copy to clipboard print? Protected void imagebutton1_click (object sender, imageclickeventargs e)    {        //define database connections and SqlCommand objects        sqlconnection  conn=new sqlconnection (configurationmanager.connectionstrings["Testconnection"). ToString ());        sqlcommand cmd=new sqlcommand ("proc_test;1", Conn);        Cmd.CommandType = CommandType.StoredProcedure;                //Specify parameter type         sqlparameter input = cmd.parameters.add ("@INPUT",  sqldbtype.int);        sqlparameter output = cmd.parameters.add ("@OUTPUT",  SqlDbType.Int);        sqlparameter return_ = cmd.parameters.add ("@RETURN",  SqLdbtype.int);                //Specify parameter direction         input. direction = parameterdirection.input;        output. direction = parameterdirection.output;        return_. direction = parameterdirection.returnvalue;           //parameter Assignment        if  (input.text  ==  "")        {            input. value = 0;        }        else        {           input. Value = convert.toint32 (Input.text);       &nbsp}           //calling stored procedures         conn.open ();        cmd.executenonquery ();        conn.close ();                   Output.Text =  Output. Value.tostring ()//Get output value        return.text = return_. Value.tostring ()//Get return value      }   reprint: http://www.cnblogs.com/lengbingshy/archive/2010/02/ 22/1671262.html

CREATE TABLE [dbo]. [Order] (
[O_ID] [bigint] IDENTITY (1,1) not for REPLICATION Not&nbs