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
— SET NOCOUNT ON added to prevent extra result sets from
— interfering with SELECT statements.

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

return @Id


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)
curId = cmd.Parameters["@Id"].Value.ToString();

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



2 thoughts on “How to use output parameter in store Procedure ?

  1. Latest October 17, 2011 / 2:59 pm

    This post couldn’t be more on the money

    • Chandra Dev October 17, 2011 / 5:03 pm

      I don’t write artical for money.

Leave a Reply

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

You are commenting using your 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 )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.