What is out parameter in sql server? how to use out parameter in stored procedure?

In this article I am going to explain about OUT Parameter in SQL SERVER. Using this out parameter how to return values from stored procedure to website.


OUT parameter is mainly used to get returned value from SQL SERVER. If you want to get particular record value from SQL SERVER then use this concept.


Create table like below

create table emp(eno int,empname varchar(50),sal bigint)

Insert some values in that table with varchar and numeric employee no.

insert into emp values('101','Ravindran','45000')
insert into emp values('102','James','25000')
insert into emp values('103','Mike','15000')
insert into emp values('104','Mathew','5000')
insert into emp values('105','James','18000')

Now we are going to create stored procedure using OUT parameter

create procedure Emp_return
@eno int,
@c1 varchar(50) output
set @c1 = (select empname from emp where eno= + @eno )

In the above procedure I have use c1 as OUT Parameter, after passed employee number stored procedure is return employee name for that specific record using out parameter.

Execute in SQL Server to test OUT Parameter

declare @t varchar(50)
exec Emp_return @eno=101,@c1=@t OUTPUT
select @t

Execute above stored procedure and get return value in Code Behind like below

using System.Data;
using System.Data.SqlClient;
public partial class _Default : System.Web.UI.Page
SqlConnection sqlcon = new SqlConnection(ConfigurationManager.ConnectionStrings["Con"].ConnectionString);
SqlCommand sqlcmd = new SqlCommand();
SqlDataAdapter da = new SqlDataAdapter();
DataTable dt = new DataTable();
protected void Page_Load(object sender, EventArgs e)
protected void Button1_Click(object sender, EventArgs e)
string i;
sqlcmd = new SqlCommand("Emp_return", sqlcon);
sqlcmd.CommandType = CommandType.StoredProcedure;
sqlcmd.Parameters.Add("@eno", SqlDbType.Int).Value = 101; //pass your textbox value here
sqlcmd.Parameters.Add("@c1", SqlDbType.VarChar, 50);
//Get here out parameter like below
sqlcmd.Parameters["@c1"].Direction = ParameterDirection.Output;
i = sqlcmd.Parameters["@c1"].Value.ToString(); //here i is get out parameter return value
da = new SqlDataAdapter(sqlcmd);
Response.Write("Employee Name : " + i);

Source code:

Client Side: ASP.NET
Code Behind: C#


I hope this article is help you to know about use of OUT Parameter in SQL SERVER.


  • OutParameter (44060-0538-OutParameter.rar)
  • Comments

    No responses found. Be the first to comment...

  • Do not include your name, "with regards" etc in the comment. Write detailed comment, relevant to the topic.
  • No HTML formatting and links to other web sites are allowed.
  • This is a strictly moderated site. Absolutely no spam allowed.
  • Name: