Return value in store procedure

Question

i have this store procedure and i want to make this that will return the value to asp .net application when it is true or false  ===================================== ALTER proc [dbo].[sp_Stock_check] @sum int ,
@product_id int
as
begin
SELECT product_id
, COUNT(stock_in) as Count_stockIn
, COUNT(stock_out) as Count_stockOut
, SUM(stock_in) as Sum_stockIn
,
SUM(stock_out)+ @sum AS Sum_StockOut,
CASE WHEN SUM(stock_in) >= (SUM(stock_out)+@Sum) THEN 'true' ELSE 'false' END AS IsInGreater
From Stock
Group By product_id
having product_id = @product_id
end

Nitin Sontakke · Accepted Answer

The stored procedure is perfect. This will return the result-set as expected by you. I would only suggest to change the 'true' and 'false' to 1 and 0 respectively, which will make it more computer friendly. There is no boolean data type in sql server. So while reading the result-set in C# you will manually have to determine it, for example, if you objStock object has a boolean property IsExcess, you will write code as follows: objStock.IsExcess = rs.getInt32(5) == 1; // 5 is the column ordinal in result-set starting from 0. Hope it helps!

Nitin Sontakke · Answer

That explains. The .ExecuteNonQuery returns not result-set, it only returns number of affected rows. You will need to use .ExecuteReader instead which will return a SqlDataReader object which you can iterate through. Google and you will find the sample code.

Imran Bashir · Answer

here i'm calling the stores procedure ============================================================== db.con.Open(); db.com = new SqlCommand("sp_Stock_check", db.con);
db.com.CommandType = CommandType.StoredProcedure;
db.com.Parameters.AddWithValue("@product_id",ddlProduct.SelectedValue);
db.com.Parameters.AddWithValue("@sum", txtQuantity.Text);
try
{
res = db.com.ExecuteNonQuery();
lblResult2.Text = "Successfully data enter";
}
catch (Exception ex)
{
lblResult.Text = ex.Message;
}
finally
{
// db.con.Close();
//}

Krishna Rajput Singh · Answer

Thank you for writing below link helpful for you..:) https://msdn.microsoft.com/en-us/library/ms188655.aspx http://www.aspsnippets.com/Articles/Return-Value-from-Stored-Procedure-in-SQL-Server-example.aspx http://stackoverflow.com/questions/9837481/return-value-in-sql-server-stored-procedure