Subscribe to Subscribers

Online Members

More...

Resources » Code Snippets » SQL

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


Posted Date:     Category: SQL    
Author: Member Level: Diamond    Points: 35


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 @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;
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.

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





  • Did you like this resource? Share it with your friends and show your love!


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

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

    Feedbacks      

    Post 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:   Sign In to fill automatically.
    Email: (Will not be published, but required to validate comment)



    Type the numbers and letters shown on the left.


    Next Resource: What is trigger in sql server? How to use trigger in sql server?
    Previous Resource: How to load/migrate data from .CSV file to mySQL table?
    Return to Resources
    Post New Resource
    Category: SQL


    Post resources and earn money!
     
    More Resources
    Popular Tags   Tag posting guidelines   Search Tags  
    OUT Parameter  .  SQL SERVER OUT parameter  .  

    Active Members
    TodayLast 7 Daysmore...

    Awards & Gifts
    Talk to Webmaster Tony John
    Copyright © SpiderWorks Technologies Pvt Ltd., Kochi, India
    2005 - 2013 All Rights Reserved.
    .NET and other trademarks mentioned in this site belong to Microsoft and other respective trademark owners.
    Articles, tutorials and all other content offered here is for educational purpose only.
    We are not associated with Microsoft or its partners.