Chandradev's Blog

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;
}

}
}