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.
Description
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.Example
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
as
Begin
set @c1 = (select empname from emp where eno= + @eno )
return
End
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 @tExecute 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;
sqlcon.Open();
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;
sqlcmd.ExecuteNonQuery();
i = sqlcmd.Parameters["@c1"].Value.ToString(); //here i is get out parameter return value
da = new SqlDataAdapter(sqlcmd);
Response.Write("Employee Name : " + i);
sqlcon.Close();
}
} Source code:
Client Side: ASP.NET
Code Behind: C#Conclusion
I hope this article is help you to know about use of OUT Parameter in SQL SERVER.