asp.net 存储过程 输入输出参数+返回值

存储过程

       USE TEST
GO
CREATE PROC SHOW --DROP PROC SHOW
@NAMES VARCHAR(50),
@PWD VARCHAR(50),
@MESSAGE VARCHAR(50) OUTPUT
AS
IF EXISTS(SELECT * FROM A WHERE NAMES=@NAMES)
BEGIN
   IF EXISTS(SELECT * FROM A WHERE NAMES=@NAMES AND PWD=@PWD)
    BEGIN
     SELECT * FROM A WHERE NAMES=@NAMES AND PWD=@PWD
     SET @MESSAGE='信息正确！'
     RETURN 1
    END
   ELSE
    BEGIN
     SET @MESSAGE='密码不正确！'
     RETURN 0
    END
END
ELSE
SET @MESSAGE='用户名不存在！'
RETURN 0

 

 

后台代码：

       SqlConnection con = new SqlConnection("server=.;database=TEST;uid=sa;pwd=;");
        con.Open();
        SqlCommand cmd = new SqlCommand("SHOW", con);
        cmd.CommandType = CommandType.StoredProcedure;

        cmd.Parameters.Add("@NAMES", SqlDbType.VarChar, 50).Value = "A";
        cmd.Parameters["@NAMES"].Direction = ParameterDirection.Input;
        cmd.Parameters.Add("@PWD", SqlDbType.VarChar, 50).Value = "S";
        cmd.Parameters["@PWD"].Direction = ParameterDirection.Input;
        cmd.Parameters.Add("@MESSAGE", SqlDbType.VarChar, 50);
        cmd.Parameters.Add("@RETURN", SqlDbType.VarChar, 50);
        cmd.Parameters["@MESSAGE"].Direction = ParameterDirection.Output;
        cmd.Parameters["@RETURN"].Direction = ParameterDirection.ReturnValue;
        cmd.ExecuteNonQuery();
        SqlDataReader sdr= cmd.ExecuteReader();

        if ((int)cmd.Parameters["@RETURN"].Value == 1)
        {
            Response.Write(cmd.Parameters["@MESSAGE"].Value);
            while (sdr.Read())
            {
                Response.Write(sdr.GetValue(0) + "/" + sdr.GetValue(1) + "/" + sdr.GetValue(2));
                Response.Write(sdr.GetInt32(0) + "/" + sdr.GetString(1) + "/" + sdr.GetString(2));
            }
        }
        else
        {
            Response.Write(cmd.Parameters["@MESSAGE"].Value);
        }