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 · 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_idddlProduct.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