Getting return value from stored procedure in C#

Solution 1:

Mehrdad makes some good points, but the main thing I noticed is that you never run the query...

SqlParameter retval = sqlcomm.Parameters.Add("@b", SqlDbType.VarChar);
retval.Direction = ParameterDirection.ReturnValue;
sqlcomm.ExecuteNonQuery(); // MISSING
string retunvalue = (string)sqlcomm.Parameters["@b"].Value;

Solution 2:

retval.Direction = ParameterDirection.Output;

ParameterDirection.ReturnValue should be used for the "return value" of the procedure, not output parameters. It gets the value returned by the SQL RETURN statement (with the parameter named @RETURN_VALUE).

Instead of RETURN @b you should SET @b = something

By the way, return value parameter is always int, not string.

Solution 3:

I was having tons of trouble with the return value, so I ended up just selecting stuff at the end.

The solution was just to select the result at the end and return the query result in your functinon.

In my case I was doing an exists check:

IF (EXISTS (SELECT RoleName FROM dbo.Roles WHERE @RoleName = RoleName)) 
    SELECT 1
ELSE
    SELECT 0

Then

using (SqlConnection cnn = new SqlConnection(ConnectionString))
{
    SqlCommand cmd = cnn.CreateCommand();
    cmd.CommandType = CommandType.StoredProcedure;
    cmd.CommandText = "RoleExists";
    return (int) cmd.ExecuteScalar()
}

You should be able to do the same thing with a string value instead of an int.