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.