I have a stored procedure that I want to return a value to a C# varaiable:
Code:
public decimal GetSiloLevelForDate(string plantId, DateTime date)
{
decimal total = 0.0M;
Open();SqlCommand cmd = new SqlCommand("getSiloLevelForDate", DbConn);
cmd.CommandType = CommandType.StoredProcedure;cmd.Parameters.Add("@.plantId", plantId);
cmd.Parameters.Add("@.date", date);SqlDataReader reader = cmd.ExecuteReader();
if (reader.Read())
{
if (!reader.IsDBNull(0))
total = reader.GetDecimal(0);}
reader.Close();Close();
return total;
}
this would normally work just fine. However, it is not becuase the actual SP's end statement is:
return (select @.tempTotal)
which should return a value. But it doesnt... if I run this SQL query:
declare @.usedTonnes numeric(13,2)
exec @.usedTonnes = dbo.getSiloLevelForDate ' 11', @.date
@.usedTonnes is a value, its 7096...
So why doesnt the C# return a value?
(so, the reader is not reading anything)Return values from stored procedures are typically used to indicatesuccess or failure, not to communicate data. A resultset or anoutput parameter is better/typically suited for this task.
The way I see it, you have 3 options:
1. Change your C# code
The way to access the return value is via a parameter with ParameterDirection = ReturnValue. You should beperforming an ExecuteNonQuery, and capturing this parameter. The datareader is overkill for what you are doing.
2. Change your stored procedure
instead of :
return (select @.tempTotal)
use:
select @.tempTotal
return
This would allow you to continue to use the datareader.
3. Change your C# code AND your stored procedure code
The way I'd suggest would be to change @.usedTonnes to an outputparameter in your stored procedure instead of a variable. In yourcode, you should beperforming an ExecuteNonQuery, and capturing this outputparameter with a parameter whose ParameterDirection = Output.
SeeInput and Output Parameters, and Return Values for more background information.|||
return(SELECT @.tempTotal) isn't correct.
The return from a stored procedure is a int. You are trying to passback a resultset through the return statement.
In addition your code is looking for a resultset not passed back from return.
Change your stored proc to end in:
SELECT @.tempTotal
return
and you won't have to change your C# code.
|||thanks for the tips guys. I did use the direction method to solve the issue:SqlCommand cmd = new SqlCommand("getSiloLevelForDate", DbConn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@.plantId", plantId);
cmd.Parameters.Add("@.date", date);
SqlParameter param = cmd.Parameters.Add("@.tempTotal", SqlDbType.Decimal);
param.Direction = ParameterDirection.ReturnValue;
cmd.ExecuteNonQuery();
total = (int)param.Value;
worked fine, and the SQL was
if( @.tempTotal is not null) begin
return @.tempTotal
end
else begin
set @.tempTotal = 0
return @.tempTotal
end
and i got the correct result|||I did end up having to use the direction method of the SqlCommand object:
SqlCommand cmd = new SqlCommand("getSiloLevelForDate", DbConn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@.plantId", plantId);
cmd.Parameters.Add("@.date", date);
SqlParameter param = cmd.Parameters.Add("@.tempTotal", SqlDbType.Decimal);
param.Direction = ParameterDirection.ReturnValue;
cmd.ExecuteNonQuery();
total = (int)param.Value;
