从存储过程返回值的方法

从存储过程返回值的方法

本文介绍了从存储过程返回值的方法的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

我有一个存储过程返回一个学生是否被锁定与否:

 返回@isLocked 

我执行这样的存储过程:

 公众诠释IsStudentLocked(字符串studentName,诠释lockoutTime)
{
的SqlConnection connObj =新的SqlConnection();
connObj.ConnectionString = Util.StudentDataInsert();
connObj.Open();

的SqlCommand COMM =新的SqlCommand(uspCheckLockout,connObj);

comm.CommandType = CommandType.StoredProcedure;

comm.Parameters.Add(新的SqlParameter(@ Studentname,studentName));
comm.Parameters.Add(新的SqlParameter(@ LockoutTime,lockoutTime));

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

//我怎样才能返回低于@isLocked价值?
回报率((INT)(@ isLocked));

}


解决方案

要使用在T-SQL的返回语句(只能返回整数值),您必须添加一个参数来检索它:

 公众诠释IsStudentLocked(字符串studentName,诠释lockoutTime)
{
的SqlConnection connObj =新的SqlConnection();
connObj.ConnectionString = Util.StudentDataInsert();
connObj.Open();

的SqlCommand COMM =新的SqlCommand(uspCheckLockout,connObj);

comm.CommandType = CommandType.StoredProcedure;

comm.Parameters.Add(新的SqlParameter(@ Studentname,studentName));
comm.Parameters.Add(新的SqlParameter(@ LockoutTime,lockoutTime));

变种returnParam =新的SqlParameter
{
参数名称=@返回,
的方向= ParameterDirection.ReturnValue
};

comm.Parameters.Add(returnParam);

comm.ExecuteNonQuery();

VAR isLocked =(INT)returnParam.Value;
connObj.Close();

返回isLocked;

}



不过,这是有点乱(IMO)。通常我在这种情况下,做的是 SELECT ,我想在我的存储过程的最后一个语句的值。然后,我用的ExecuteScalar 的命令对象检索值,而的ExecuteNonQuery



PROC:

  ... ... SQL 

选择@ isLocked

方法:

 公众诠释IsStudentLocked(字符串studentName,诠释lockoutTime)
{$ b $使用b(SqlConnection的connObj =新的SqlConnection())
{
connObj.ConnectionString =的Util。 StudentDataInsert();
connObj.Open();

的SqlCommand COMM =新的SqlCommand(uspCheckLockout,connObj);

comm.CommandType = CommandType.StoredProcedure;

comm.Parameters.Add(新的SqlParameter(@ Studentname,studentName));
comm.Parameters.Add(新的SqlParameter(@ LockoutTime,lockoutTime));

回报(INT)comm.ExecuteScalar();
}
}


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));

    }
解决方案

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();
    }
}

这篇关于从存储过程返回值的方法的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!

07-26 14:59