Ref, out, and output in SQL

Source: Internet
Author: User
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!

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.