database return issue

Hello all!

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;