How to use output parameter in store Procedure ?


Output parameter is used to pass the value after execution of sqlquery.
For example , tblemp table is there with Id,EmpName,EmpSal , I have to insert record on that table and I have to fetch the latest EmpId from tblemp for other operation then we can use Output parameter in storeprocedure like this example. It will avoid the multiple time database hit.

Store procedure for insert

ALTER PROCEDURE [dbo].[InsertEmpSp_WithOutPutParameter]
— Add the parameters for the stored procedure here
@EmpName varchar(150),
@EmpSal varchar(150),
@Id int output
AS
BEGIN
— SET NOCOUNT ON added to prevent extra result sets from
— interfering with SELECT statements.
SET NOCOUNT ON;

— Insert statements for procedure here
insert into tblEmp(EmpName,EmpSal) values(@EmpName,@EmpSal)

set @Id=SCOPE_IDENTITY()
return @Id

END

Code behind page is like this

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

public partial class OutPut_ParaMeter_Sp : System.Web.UI.Page
{
SqlConnection con = new SqlConnection("Data Source=ITTIOS02;Initial Catalog=Test;Integrated Security=True");
protected void Page_Load(object sender, EventArgs e)
{

}
protected void BtnSubmit_Click(object sender, EventArgs e)
{
string curId = "";
using (SqlCommand cmd = new SqlCommand("InsertEmpSp_WithOutPutParameter", con))
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@EmpName", txtEmpName.Text);
cmd.Parameters.AddWithValue("@EmpSal", txtEmpSal.Text);
cmd.Parameters.Add("@id", SqlDbType.Int).Direction = ParameterDirection.Output;
using (con)
{
con.Open();
cmd.ExecuteNonQuery();
curId = cmd.Parameters["@Id"].Value.ToString();
con.Close();
}

txtEmpName.Text = "";
txtEmpSal.Text = "";
Label1.Text = "Data has been inserted sucessfully.Current latest EmpId is " + curId;
}

}
}

Advertisements

2 Responses to “How to use output parameter in store Procedure ?”

  1. Latest Says:

    This post couldn’t be more on the money

    • Chandra Dev Says:

      Hi
      I don’t write artical for money.


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: