How to start a stored procedure (with parameters - has a return value) From the code behind?

Question

How can I use a stored procedure (with parameters - has a return value of type int ) from the code behind?

My stored procedure is as follows:

 ALTER Procedure [dbo].[sp_Noskheh_SumOfTotalPay] @Co_ID int AS ----------------- Declare @Sum bigint ----------------- BEGIN SELECT @Sum = SUM(TotalPay) FROM Noskheh WHERE (Co_ID = @Co_ID) RETURN @Sum END 

I want to use @Sum in the code behind ...

Could you show me a way to do this?

Thanks in advance

Best wishes

Answer 1

You need to configure SqlConnection and SqlCommand . If you have code with the RETURN @Sum operator at the end, you need to do this (define a parameter of type RETURN_VALUE):

 using(SqlConnection _conn = new SqlConnection(-your-connection-string-here)) using(SqlCommand _cmd = new SqlCommand("dbo.sp_Noskheh_SumOfTotalPay", _conn)) { _cmd.CommandType = CommandType.StoredProcedure; _cmd.Parameters.Add(new SqlParameter("@CO_ID", SqlDbType.Int)); _cmd.Parameters["@CO_ID"].Value = 5; // whatever value you want _cmd.Parameters.Add(new SqlParameter("@RETURN_VALUE", SqlDbType.BigInt)); _cmd.Parameters["@RETURN_VALUE"].Direction = ParameterDirection.ReturnValue; _conn.Open(); _cmd.ExecuteNonQuery(); Int64 result = Int64.Parse(_cmd.Parameters["@RETURN_VALUE"].Value); _conn.Close(); } 

It would be much simpler if you replace this RETURN statement with a simple SELECT:

 SELECT @Sum 

In this case, you can use the simplified version that I had before - using .ExecuteScalar() to get the only value of one line returned from the stored proc:

 using(SqlConnection _conn = new SqlConnection(-your-connection-string-here)) using(SqlCommand _cmd = new SqlCommand("dbo.sp_Noskheh_SumOfTotalPay", _conn)) { _cmd.CommandType = CommandType.StoredProcedure; _cmd.Parameters.Add(new SqlParameter("@CO_ID", SqlDbType.Int)); _cmd.Parameters["@CO_ID"].Value = 5; // whatever value you want _conn.Open(); object result = _cmd.ExecuteScalar(); _conn.Close(); Int64 sum = Int64.Parse(result); } 

This should call your saved proc, read the one value you return, and convert it to an int variable called sum .

Answer 2

There is no shortage of textbooks on this subject .

Answer 3

You can add SqlParameter @Sum with Direction set to ReturnValue

Example:

 SqlCommand cmd = new SqlCommand(): cmd.Connection = // place your SqlConnection object; cmd.CommandType = CommandType.StoredProcedure; cmd.CommandText = "StoreProcedureName"; cmd.Parameters.Add("@Sum", SqlDbType.BigInt).Direction = ParameterDirection.ReturnValue