Nov 20

How to retrieve the RETURN value from a stored procedure in .NET

If you need to return a single integer from a stored procedure, it’s more efficient to use the RETURN statement than SELECT since SELECT is a whole recordset with a cursor. To access the return value from your C# code, you add a “ReturnValue” parameter to your stored procedure call:

(Pretend you have an open SqlConnection called “conn”.)

SqlCommand sqlCommand = new SqlCommand(“webmail_getMessageCount”);
sqlCommand.Connection = conn;
sqlCommand.CommandType = CommandType.StoredProcedure;

SqlParameter messageCount = new SqlParameter(“@count”, SqlDbType.Int);
messageCount.Direction = ParameterDirection.ReturnValue;
sqlCommand.Parameters.Add(messageCount);

sqlCommand.ExecuteNonQuery();

return (int)messageCount.Value;