Can you use cmd.ExecuteScalar when the sproc uses RETURN @value

Question

Can you use

int blah = Convert.ToInt32(cmd.ExecuteScalar());

When the sproc's last statement does:

RETURN @value

I can only get it to work if it does:

SELECT @value

Also, this gives me a object null exception:

int blah = (int)cmd.ExecuteScalar();

isn't convert.toint32 and (int) the same thing but one is a wrapper of the other?

Answer 1

No, you cannot. The ExecuteScalar() method is designed to return as single value that is returned in a result set. Basically, the value in the first column of the first row returned.

To get the return value, you need to add a parameter to your SQLCommand object. Use the name "@RETURN_VALUE" and specify a parameter direction of Return when creating the parameter object. You can then use the ExecuteNonQuery() method.

I must note that IMO, stored procedure return values should simply indicate the status of the procedure. All data should be returned through result sets or output parameters.