CODEWITHSUNDEEP
c#stored-procedures
user793468
user793468

Reputation: 4966

Returning a value from a stored procedure to a method

I have a stored procedure which returns whether a student is locked or not:

RETURN @isLocked

I execute this stored procedure like:

    public int IsStudentLocked(string studentName, int lockoutTime)
    {
        SqlConnection connObj = new SqlConnection();
        connObj.ConnectionString = Util.StudentDataInsert();
        connObj.Open();

        SqlCommand comm = new SqlCommand("uspCheckLockout", connObj);

        comm.CommandType = CommandType.StoredProcedure;

        comm.Parameters.Add(new SqlParameter("@Studentname", studentName));
        comm.Parameters.Add(new SqlParameter("@LockoutTime", lockoutTime));

        comm.ExecuteNonQuery();
        connObj.Close();

        //How can I return the @isLocked value below?
        return ((int)(@isLocked));

    }

Upvotes: 4

Views: 13791

Answers (3)

Pein
Pein

Reputation: 1246

You should call ExecuteScalar instead of ExecuteNonQuery and replace RETURN with SELECT in your stored proc.

By the way, you should wrap your connection with using block, so it will be properly disposed even in case when some exception occured before Close().

Upvotes: 3

HatSoft
HatSoft

Reputation: 11191

If @IsLocked an output parameter in your Stored Procedure

System.Data.SqlClient.SqlParameter paramterIsLockedOut = command1.Parameters.Add("@MyParameter", SqlDbType.SmallInt);
paramterIsLockedOut.Direction = ParameterDirection.Output;
command1.ExecuteNonQuery();

int newValue = (int)paramterIsLockedOut.Value;

Upvotes: 0

rossipedia
rossipedia

Reputation: 59367

To use the RETURN statement in T-SQL (which can ONLY return integer values), you have to add a parameter to retrieve it:

public int IsStudentLocked(string studentName, int lockoutTime)
{
    SqlConnection connObj = new SqlConnection();
    connObj.ConnectionString = Util.StudentDataInsert();
    connObj.Open();

    SqlCommand comm = new SqlCommand("uspCheckLockout", connObj);

    comm.CommandType = CommandType.StoredProcedure;

    comm.Parameters.Add(new SqlParameter("@Studentname", studentName));
    comm.Parameters.Add(new SqlParameter("@LockoutTime", lockoutTime));

    var returnParam = new SqlParameter
    {
        ParameterName = "@return",
        Direction = ParameterDirection.ReturnValue
    };

    comm.Parameters.Add(returnParam);

    comm.ExecuteNonQuery();

    var isLocked = (int)returnParam.Value;
    connObj.Close();

    return isLocked;

}

However, this is kinda messy (IMO). Usually what I do in this case is to SELECT the value that I want as the last statement in my stored procedure. Then I use ExecuteScalar on the command object to retrieve the value instead of ExecuteNonQuery.

Proc:

... SQL ...

SELECT @isLocked

Method:

public int IsStudentLocked(string studentName, int lockoutTime)
{
    using(SqlConnection connObj = new SqlConnection())
    {
        connObj.ConnectionString = Util.StudentDataInsert();
        connObj.Open();

        SqlCommand comm = new SqlCommand("uspCheckLockout", connObj);

        comm.CommandType = CommandType.StoredProcedure;

        comm.Parameters.Add(new SqlParameter("@Studentname", studentName));
        comm.Parameters.Add(new SqlParameter("@LockoutTime", lockoutTime));

        return (int)comm.ExecuteScalar();
    }
}

Upvotes: 18

Related Questions