Menu
Is it not possible to get the return value of a stored procedeure when using a datareader? The return value is always null, but the SP returns a valid int from within SSMS.
myCommand.CommandText = "GetVenueVideos";
SqlParameter retVal = new SqlParameter("@returnValue",SqlDbType.Int);
retVal.Direction = ParameterDirection.ReturnValue;
myCommand.Parameters.Add(retVal);
myReader = myCommand.ExecuteReader();
if (myReader.Read() && myReader.HasRows)
{
int returnValue = Convert.ToInt32(retVal.Value);
//returnValue is null at this point
}
493
As explained earlier, the SqlDataReader returns data via a sequential stream. To read this data, you must pull data from a table row-by-row Once a row has been read, the previous row is no longer available.
You can use one or more RETURN statements in a stored procedure. The RETURN statement can be used anywhere after the declaration blocks within the SQL-procedure-body. To return multiple output values, parameters can be used instead. Parameter values must be set prior to the RETURN statement being executed.
Stored procedures do not have a return value but can take a list with input, output, and input-output parameters.
You can use the return statement inside a stored procedure to return an integer status code (and only of integer type). By convention a return value of zero is used for success. If no return is explicitly set, then the stored procedure returns zero.
Agh. I've found out the reader has to be closed to get the return value! So inside the if {} block above, I added:
myReader.Close(); // Need to close data reader before getting return value.
int returnValue = (int)myCommand.Parameters["@returnValue"].Value;
...and now it works OK!
106
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With