Return 返回值
使用返回值表示存储过程的执行状态,它类似于输出参数,其区别:(1)返回值只能返回sql整数值;
(2)返回值不能在存储过程内部声明,它使用Transcat-SQL的RETURN语句返回。
看例子:
CREATE PROCEDURE User_Login
(
@LoginID varchar(50),
@UsrPassword varchar(50)
)
AS
if exists
(
SELECT USERID FROM t_user WHERE LoginID = @LoginID AND UsrPassword = @UsrPassword
)
-- 登录成功
RETURN 0
if exists
(
SELECT USERID FROM t_user WHERE LoginID = @LoginID
)
-- 登录失败 密码错误
RETURN 1
-- 登录失败 用户名不正确
RETURN 2
GO
C#调用的例子:
private int test(string as_user,string as_pass)
{
string procedure = "User_Login";
string connectionString = "server=localhost;database=db;User Id=sa;pwd=password;";
// Create ADO.NET objects.
SqlConnection con = new SqlConnection(connectionString);
SqlCommand cmd = new SqlCommand(procedure, con);
// Configure command and add input parameters.
cmd.CommandType = CommandType.StoredProcedure;
SqlParameter param;
cmd.Parameters.Add("@ReturnValue", SqlDbType.Int, 4).Direction = ParameterDirection.ReturnValue;
param = cmd.Parameters.Add("@LoginID", SqlDbType.VarChar, 50);
param.Value = as_user;
param = cmd.Parameters.Add("@UsrPassword", SqlDbType.VarChar, 50);
param.Value = as_pass;
con.Open();
cmd.ExecuteNonQuery();
int li_r = cmd.Parameters["@ReturnValue"].Value;
con.Close();
return li_r;
}