How to retrieve store procedure output parameter value?I have given a simple code example.
I have created a store procedure with output parameter. After that I get it from code behind page of
asp.net and stored it a variable.
private void GetInfo()
{
DALUtility objDALUtility = null;
SqlConnection con = null;
SqlCommand cmd = null;
SqlDataAdapter da = null;
try
{
objDALUtility = new DBUtility(ConfigurationManager.AppSettings["myconString"]);
con = objDALUtility.GetDBConnection();
con.Open();
cmd = new SqlCommand();
cmd.Connection = con;
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandTimeout = 500;
cmd.CommandText = "usp_MyInfo";
cmd.Parameters.AddWithValue("@Id", strId);
cmd.Parameters.AddWithValue("@empName", Convert.ToString(Session["Name"]));
cmd.Parameters.Add("@TotalCount", SqlDbType.Int).Direction = ParameterDirection.Output;
da = new SqlDataAdapter(cmd);
DataSet objDS = new DataSet("MyInfo");
da.Fill(objDS);
con.Close();
int Total = Convert.ToInt32(cmd.Parameters["@TotalCount"].Value);
}
}
Stored Procedure
CREATE Proc [dbo].[usp_MyInfo]
@Id int,
@Name varchar(50) = null,
@Total int = 0 OUTPUT
AS
SET @Total = (Select COUNT(Distinct tbl_Logs.ContentId) From tbl_Logs with(nolock)
Inner Join tbl_Logs_details with(nolock) on tbl_Logs_details.ContentId = tbl_Logs.ContentId
Where id = @id and tbl_Logs_details.ContentId = 2
Select @Total as Count
0 comments:
Post a Comment