Working with OUTPUT Parameters in Stored Procedure

vishalneeraj-24503
Posted by in Sql Server category on for Intermediate level | Points: 250 | Views : 2997 red flag
Rating: 3 out of 5  
 1 vote(s)

In this article, we will learn about Working with OutPut parameters in Stored Procedure.

Introduction


The OUTPUT parameters are used to send the OUTPUT from a procedure.This is a write-only parameter i.e, we cannot pass values to OUTPUT parameters while executing the stored procedure but we can assign values to OUTPUT parameter inside the stored procedure and the calling program can receive this output value.

OUTPUT Parameters are used to fetch records from db and are very much similar with Out and Ref keyword in .NET.Output parameters are used to hold values as Reference variables.They are not required to return from Stored Procedure.

We specify the OUTPUT keyword for a parameter in the procedure definition.We can have multiple OUTPUT parameters in Stored procedure.

In addition, we should specify the OUTPUT keyword when executing a stored procedure containing the output parameter to get the value from Stored Procedure.

In SQL Server database,there are two ways with we can pass parameters to procedures.

1). InPut Parameter and
2). OutPut Parameter

InPut Prameter:- These types of parameters are used to send values to stored procedures.

OutPut parameter:- These types of parameters are used to get values from stored procedures.This is similar to a return type in functions.


The General syntax to create an OUTPUT parameter is:-



Objective

Working with OUTPUT parameters in Stored Procedure.

Using the code


We can understand it with the help of Stored Procedure:-

Below is Employee_Master Table Structure and Data:-






Now,we will create Store Procedure as



If we execute above Stored Procedure,then we will get Employee Code by Passing Employee-Id as shown below:-




In Code-behind,we can write to get the same output,we have to give ParameterDirection.Output
if working with Output parameters.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Data.SqlClient;

private string get_employee_code_by_employee_id(int employee_id)
{
string employee_code = string.Empty;
SqlConnection con = null;

try
{
con = new SqlConnection(Convert.ToString(System.Configuration.ConfigurationManager.ConnectionStrings["DataBaseConnection"].ConnectionString));
SqlCommand cmd = new SqlCommand();
cmd.Connection = con;

con.Open();

cmd.CommandText = "sp_get_employee_code_by_employee_id";
cmd.CommandType = CommandType.StoredProcedure;

cmd.Parameters.AddWithValue("@employee_id", employee_id);

cmd.Parameters.Add("@employee_code", SqlDbType.VarChar, 15);
cmd.Parameters["@employee_code"].Direction = ParameterDirection.Output;

cmd.ExecuteNonQuery();

if (cmd.Parameters["@employee_code"].Value != null)
employee_code = Convert.ToString(cmd.Parameters["@employee_code"].Value);
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (con != null)
con.Close();
}

return employee_code;

}

protected void btn_get_Click(object sender, EventArgs e)
{
try
{
string employee_code = get_employee_code_by_employee_id(1);
}
catch (Exception ex)
{
throw ex;
}
}




Conclusion


 In this article we have learned about OUTPUT parameters and it's implementation.



Page copy protected against web site content infringement by Copyscape

About the Author

vishalneeraj-24503
Full Name: vishal kumar
Member Level: Platinum
Member Status: Member,MVP
Member Since: 11/5/2013 5:58:17 AM
Country: India

http://www.dotnetfunda.com

Login to vote for this post.

Comments or Responses

Login to post response

Comment using Facebook(Author doesn't get notification)