SQL Server output parameter

Output Parameter

Store procedure is a set of some sql statement which saved on server. Store procedure takes argument for database operation which is known parameter. There are two type of parameter.

Input parameter

When user provides a value as parameter and passes to store procedure.

Output Parameter

When user hold something returns from store procedure through parameter known as output parameter.

Advantage

• Decrease complexity because it help you to do more than one task in single procedure
• when inserting data to a table and you need to get the identity value back
• when perfoming select statements and you need some extra data,
• when updating or inserting data and you need some way to know if the operation was successful
• for most if not all of the reasons you need out or ref parameters in c#

Store Procedure

USE [Girfa]

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

ALTER PROCEDURE [dbo].[pcheck]

(

@id bigint,

@phone varchar(15) output

)

as

as

select @phone= MobileNo from dbo.UserMaster where UserId=@id

C# Code

UserModel ob = new UserModel();

try

 {

    using (SqlConnection dbCon = new  SqlConnection(ConfigurationManager.ConnectionStrings["dbConnection"].ToString()))

    {

 

        using (SqlCommand cmd = new SqlCommand("[dbo].[pcheck]", dbCon))

        {

            cmd.CommandType = CommandType.StoredProcedure;

            cmd.Parameters.AddWithValue("@id", Convert.ToInt32(id));

            cmd.Parameters.Add("@phone", SqlDbType.VarChar, 30);

            cmd.Parameters["@phone"].Direction = ParameterDirection.Output;

            if (dbCon.State == ConnectionState.Closed)

                    dbCon.Open();

            cmd.ExecuteNonQuery();

            b.MobileNo = Convert.ToString(cmd.Parameters["@phone"].Value);

        }

 

     }

}

catch (Exception ex)

{

              

}

 

return ob;

 Next Example