Stored Procedure output parameters, return values, returned tables, and C # calls,

Source: Internet
Author: User
Tags management studio sql server management sql server management studio

Stored Procedure output parameters, return values, returned tables, and C # calls,

Output variables, return values, and result sets can be defined during stored procedures. The default return value for each stored procedure is 0. Next, a new Stored Procedure written based on the Stored Procedure created in SQL Server and C # called contains the output parameters, return values, and select results.

USE [db]GO/****** Object:  StoredProcedure [dbo].[insert_persions]    Script Date: 2/25/2015 11:14:11 AM ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOIF OBJECT_ID ('get_persons','p') IS NOT NULL    DROP PROCEDURE get_persons;GO-- =============================================-- Author:      <Author,,Name>-- Create date: <Create Date,,>-- Description: <Description,,>-- =============================================CREATE PROCEDURE get_persons    -- Add the parameters for the stored procedure here    @firstname varchar(255),    @ret int = 0 OUTPUTASBEGIN    -- SET NOCOUNT ON added to prevent extra result sets from    -- interfering with SELECT statements.    SET NOCOUNT ON;    -- Insert statements for procedure here    SELECT * FROM Persons    WHERE FirstName = @firstname;    SET @ret = @@ROWCOUNT    RETURN 10ENDGODECLARE @retVal int, @status intEXECUTE @status = get_persons "San",@ret = @retVal OUTPUTSELECT @retVal as N'Output val',@status as N'Ruturn val'

Output Parameters
The OUTPUT parameter definition location is the same as the general parameter of the stored procedure, but an OUTPUT keyword is added later. In the process body, specify the output value for the parameter using SET.
When executing an SQL statement that contains OUTPUT parameters to obtain the process body, you also need to define the variables first, and then pass the variables as parameters into the execution statement and add the OUTPUT. As shown in the preceding example: EXECUTE get_persons "San ",@ Ret = @ retVal OUTPUT

Return Value
The default return value of a stored procedure is 0. However, you can add the return value statement at the end of the procedure or at the end of a branch block.
When executing the process body, if you need to get the returned value at the same time, you can first define the variable, and then use "variable =" to put it After EXECUTE to get the execution result value. Example: EXECUTE@ Status =Get_persons "San", @ ret = @ retVal OUTPUT

In the preceding example, after the Stored Procedure statement is executed in SQL Server Management Studio, a Table result is generated for each select statement, as shown in:

C # Call
The preceding example shows how SQL statements handle output parameters, return values, and SELECT tables. If we need to use C # To execute the stored procedure and want to get all these results, we can use the following code:

            String conStr = @"Data Source=HOST\SQLEXPRESS;Initial Catalog=DB;Integrated Security=SSPI";            SqlConnection con = new SqlConnection(conStr);            try            {                con.Open();                Console.WriteLine("Connect Sucess!");                SqlCommand com = new SqlCommand();                com.CommandType = System.Data.CommandType.StoredProcedure;                com.Connection = con;                com.CommandText = "get_persons";                SqlParameter pFirstName = new SqlParameter("@firstname", "San");                SqlParameter para = new SqlParameter("@ret", SqlDbType.Int);                para.Direction = ParameterDirection.Output;                 com.Parameters.Add(pFirstName);                com.Parameters.Add(para);                com.Parameters.Add("@status", SqlDbType.Int);                com.Parameters["@status"].Direction = ParameterDirection.ReturnValue;                SqlDataReader sdr = com.ExecuteReader();                while (sdr.Read())                {                    for (int i = 0; i < sdr.FieldCount; i++)                        Console.Write(sdr[i].ToString() + " ");                    Console.WriteLine();                }                sdr.Close();                Console.WriteLine(com.Parameters["@ret"].Value);                Console.WriteLine(com.Parameters["@status"].Value);            }            catch (Exception e)            {                Console.WriteLine(e.ToString());            }            finally            {                con.Close();            }

We can see that both OUTPUT parameters and return values Add the same parameter names and types as stored procedures, and specify ParameterDirection as OUTPUT and RETURNVALUE respectively. After obtaining the execution result, you can obtain the Value of the command parameter "com. Parameters [" @ ret "]. Value" after the command is executed ".

If you do not need to obtain the SELECT result, run ExecuteNonQuery. But what if we still want to get the table result from the execution of the stored procedure? At this time, one way is to use "SqlDataReader sdr = com. ExecuteReader ();" to execute and then Read the SELECT result through sdr. Read. It should be noted that, according to personal practice, the return parameters and return values can be obtained only after the sdr completes reading the relationship.

Related Article

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.