Scenario - My stored procedure just returns some integer values depending on different conditions. There are no OUT or any other parameters involved for returning values from the stored procedure.
e.g.
IF (EXISTS( SELECT * FROM TableName WHERE UserId = @UserId AND RoleId = @RoleId))
RETURN(1)
ELSE
RETURN(0)
If you notice, there are no output parameters involved here. The return value can be accessed from code (C# for example), like this:
SqlParameter returnValueParam = command.Parameters.Add("@return_value", SqlDbType.Int);
returnValueParam.Direction = ParameterDirection.ReturnValue;
command.ExecuteNonQuery();
int returnValue = (int)returnValueParam.Value;