Ref, out, and output in SQL

When do I need to use ref and out?

1. Sometimes, we need to get the running status of a value in the method. According to the defined method, we can only get a return value. However, sometimes we may want to get multiple values, this information cannot be returned through the return value. We can use ref or out before the parameter to obtain multiple return values.
2. When executing the SQL stored procedure, we can return corresponding values through the SQL statement running status in the stored procedure. SQL return only supports return values in int format. Multiple return values can be obtained by using ref or out and displayed to the page.

What is the difference between ref and out:

WhenCodeThis parameter is required during running and the required value is returned using this parameter.

If you only want to obtain multiple return values without using these parameters in the method, you can use out.

Example

Example 1: Use ref and out on the Asp.net page

 Protected void Page_load ( Object Sender, Eventargs E ){ String Str1, str2, S1 = "First parameter" , S2 ="Second parameter" ; // Run the following code and the following error is displayed: // use the unassigned local variable "str1" // useref (ref str1, ref str2); // output the result: // use the first parameter of Out // use the second parameter of out Useout ( Out Str1, Out Str2); response. Write (str1 + "<Br/>" ); Response. Write (str2 + "<Br/>" ); // Output result: // The first parameter uses ref // The second parameter uses ref Useref ( Ref S1, Ref S2); response. Write (S1 + "<Br/>" ); Response. Write (s2 + "<Br/>" );} Public void Useout ( Out string Str1, Out string Str2) {str1 = "Use the first parameter of out" ; Str2 = "Use the second out parameter" ;} Public void Useref ( Ref string S1, Ref string S2) {S1 + = "Using ref" ; S2 + = "Using ref" ;}

Example 2: when the output keyword is used for parameters in the stored procedure, it corresponds to the C # code, which is ref and out.

The relationship between ref and out and output is as follows:

Parameterdirection	Description	Corresponding to C #	Corresponding SQL
Input	Input
InputOutput	Input and Output	Ref	Corresponding output
Output	Output	Out
Returnvalue	Return Value

Example:

Create an employee table:

The data in the table is as follows:

The stored procedure is as follows:

 Alter procedure DBO. insertemployee (@ employeename Nvarchar (20), @ employeeage Int = Null , @ Employee1_mentid Int = Null , @ Employeescore Int = Null , @ Outvalue Nvarchar (20) Output ) As /* Set nocount on */  If exists ( Select * From Employee Where Employeename = @ employeename) Begin set @ Outvalue = 'Username' + @ Employeename + 'Repeat! '  Return end insert Employee (employeename, employeeage, employeedeparmentid, employeescore) Values (@ Employeename, @ employeeage, @ employee1_mentid, @ employeescore) Set @ Outvalue = 'Users' + @ Employeename + 'Created successfully! '  Return 

The page code is as follows:

 String Connectionstring = Configurationmanager . Connectionstrings [ "Dbstconnectionstring" ]. Connectionstring; Sqlconnection Conn = New  Sqlconnection (Connectionstring ); Sqlcommand Cmd = conn. createcommand (); cmd. commandtype = Commandtype . Storedprocedure; cmd. commandtext = "Insertemployee" ; Cmd. Parameters. addwithvalue ( "@ Employeename" , "Song ba" ); Cmd. Parameters. addwithvalue ( "@ Employeeage" , 20); cmd. Parameters. addwithvalue ( "@ Employee1_mentid" , 1); cmd. Parameters. addwithvalue ( "@ Employeescore" , 95 );// Note param_outvalue.direction // you can set param_outvalue.direction = output. You only need to declare the parameter. // output // sqlparameter param_outvalue = new sqlparameter ("@ outvalue", sqldbtype. nvarchar, 20); // param_outvalue.direction = parameterdirection. output; // cmd. parameters. add (param_outvalue); // pay attention to param_outvalue.direction // set it to param_outvalue.direction = InputOutput. The parameter needs to be initialized // output in the corresponding database  Sqlparameter Param_outvalue = New  Sqlparameter ( "@ Outvalue" , Sqldbtype . Nvarchar, 20); param_outvalue.direction = Parameterdirection . InputOutput; param_outvalue.value =String . Empty; cmd. Parameters. Add (param_outvalue); Conn. open (); cmd. executenonquery (); Conn. Close (); response. Write (param_outvalue.value );

Output result of the first running:

User song BA was successfully established!

Output result of the second operation:

Repeated username song 8!