如何从SQL存储过程中获取返回值 [英] How to get return value from SQL stored procedure

问题描述

你好，

我从存储过程中获取返回参数有问题



我想要选择记录，如果它不等于我已经传递给存储过程的性别（我通过性别作为输入参数男性或女性）。



首先我要检查记录是否存在

如果存在那么

检查它的性别不等于我传递给存储过程的性别。（我想选择相反的性别）

条件是真的然后选择记录

如果条件为假则返回1

如果记录不存在则返回2



我的代码如下。

  ALTER  程序 [db_owner]。[SelectProfileByID] 
  @ ProfileID   VARCHAR （ 6 ），
  @ UserGender   VARCHAR （ 10 ），
  @ SelectStatus  INTEGER 输出 
  AS  
  BEGIN  
 
  IF   EXISTS （ SELECT  *  FROM  tblUserInfo  WHERE  ProfileID = @ ProfileID  AND  IsDeleted = 0）
  BEGIN  
 
 
 
  IF   EXISTS （ SELECT  *  FROM  tblUserInfo  WHERE  ProfileID = @ ProfileID  AND  UserGender！=  @UserGender ）
  BEGIN  
 
  SELECT  tblUserInfo.UserAutoID，tblUserInfo.UserFirstName，tblUserInfo.ProfileID，
年（GETDATE（）） - 年（UserDOB） AS  UserDOB，tblUserInfo.Height，tblUserInfo.Religion，tblUserInfo.Education，tblUserInfo.Community，tblUserInfo.MaritalStatus，tblUserInfo.FamilyLocation，
 tblUserInfo.Occupation，tblUserInfo.ProfilePicPath ，tblInterest.InterestStatus 
  FROM  
 tblUserInfo 
  LEFT   JOIN  tblInterest 
  ON  
 tblInterest.InterestIn = tblUserInfo.UserAutoID 
  WHERE  
 tblUserInfo.ProfileID =  @ ProfileID  
 
  END  
 
  ELSE  
  BEGIN  
 
 
  RETURN   SET  @ SelectStatus =  1  
 
  END  
 
  END  
  ELSE  
  BEGIN  
 
  RETURN   SET  @ SelectStatus =  2  
 结束 
 
 
  END  

C＃代码..

  public  UserInfo SearchIdInfo（ string  ProfileId， string 性别， ref   int  SelectStatus）
 {
 UserInfo info =  new  UserIn FO（）; 
 SqlDataReader rdr =  null ; 
 
 尝试 
 {
  if （sqlcon。 State == ConnectionState.Closed）
 {
 sqlcon.Open（）; 
} 
 SqlCommand cmd =  new  SqlCommand（  db_owner.SelectProfileByID，sqlcon）; 
 cmd.CommandType = CommandType.StoredProcedure; 
 SqlParameter sprmparam =  new  SqlParameter（）; 
 sprmparam = cmd.Parameters.Add（  @ ProfileID，SqlDbType.VarChar）; 
 sprmparam.Value = ProfileId; 
 sprmparam = cmd.Parameters.Add（  @ UserGender，SqlDbType.VarChar）; 
 sprmparam.Value =性别; 
 
 rdr = cmd.ExecuteReader（）; 
  //   SelectStatus = SelectStatus;  
  if （SelectStatus ==  0 ）
 {
  if （rdr.Read（））
 {
 info.UserAutoID = rdr [ 0 ]。ToString（）; 
 info.UserFirstNamea = rdr [ 1 ]。ToString（）; 
 info.ProfileID = rdr [ 2 ]。ToString（）; 
 info.Age = rdr [ 3 ]。ToString（）; 
 info.Height = rdr [ 4 ]。ToString（）; 
 info.Religion = rdr [ 5 ]。ToString（）; 
 info.Education = rdr [ 6 ]。ToString（）; 
 info.Community = rdr [ 7 ]。ToString（）; 
 info.MaritalStatus = rdr [ 8 ]。ToString（）; 
 info.FamilyLocation = rdr [ 9 ]。ToString（）; 
 info.Occupation = rdr [ 10 ]。ToString（）; 
 info.ProfilePicPath = rdr [ 11 ]。ToString（）; 
 info.InterestStatus = rdr [ 12 ]。ToString（）; 
} 
} 
} 
  catch  
 {
 
} 
 最后 
 {
 rdr.Close（）; 
 sqlcon.Close（）; 
 
} 
 返回信息; 

我尝试了什么：



你可以看到上面的代码来理解我要尝试的东西..

解决方案

您需要在 cmd 对象上定义输出参数，例如

 cmd.Parameters.Add（  @ SelectStatus，SqlDbType.VarChar）; 
 cmd.Parameters [  @ SelectStatus]。Direction = ParameterDirection.Output; 

然后在调用SP之后，您可以使用解决方案1中建议的方法，但是当您返回一个整数时，您应该使用

（int）Command.Parameters [@ SelectStatus]。值; 

  string  retunvalue =（ string ）cmd.Parameters [  @ SelectStatus]值; 

hello,
I have a problem to get return parameter from stored procedure

I want select record if it is not equal to gender which I have pass to stored procedure(I pass gender as input parameter Male or Female ).

firstly I want to check record is exist or not
If Exist Then
Check Its Gender is not equal to Gender which I pass to stored procedure.(I want to select opposite gender)
I condition is true then select record
If condition is false then return 1
If Record not exist then return 2

My code as bellow.

ALTER PROCEDURE [db_owner].[SelectProfileByID]
	@ProfileID VARCHAR(6),
	@UserGender VARCHAR(10),
	@SelectStatus INTEGER OUTPUT
AS
BEGIN

	IF EXISTS(SELECT * FROM tblUserInfo WHERE ProfileID=@ProfileID AND IsDeleted=0)
	BEGIN
	
		
	
	IF EXISTS(SELECT * FROM tblUserInfo WHERE ProfileID=@ProfileID AND  UserGender != @UserGender)
	BEGIN

	SELECT tblUserInfo.UserAutoID,  tblUserInfo.UserFirstName, tblUserInfo.ProfileID,
		YEAR(GETDATE())-YEAR(UserDOB) AS UserDOB,tblUserInfo.Height,tblUserInfo.Religion, tblUserInfo.Education,tblUserInfo.Community,tblUserInfo.MaritalStatus,tblUserInfo.FamilyLocation,
		tblUserInfo.Occupation,tblUserInfo.ProfilePicPath,tblInterest.InterestStatus
		FROM
		tblUserInfo
		LEFT JOIN tblInterest
		ON 
		tblInterest.InterestIn=tblUserInfo.UserAutoID
		WHERE
		tblUserInfo.ProfileID= @ProfileID
		
		END
		
		ELSE
		BEGIN
				
		
		RETURN SET @SelectStatus= 1

		END

		END
		ELSE
		BEGIN
		
		RETURN SET @SelectStatus= 2
		END
		
	
END

C# Code..

public UserInfo SearchIdInfo(string ProfileId, string Gender,ref int SelectStatus)
        {
            UserInfo info = new UserInfo();
            SqlDataReader rdr = null;

            try
            {
                if (sqlcon.State == ConnectionState.Closed)
                {
                    sqlcon.Open();
                }
                SqlCommand cmd = new SqlCommand("db_owner.SelectProfileByID", sqlcon);
                cmd.CommandType = CommandType.StoredProcedure;
                SqlParameter sprmparam = new SqlParameter();
                sprmparam = cmd.Parameters.Add("@ProfileID", SqlDbType.VarChar);
                sprmparam.Value = ProfileId;
                sprmparam = cmd.Parameters.Add("@UserGender", SqlDbType.VarChar);
                sprmparam.Value = Gender;

                rdr = cmd.ExecuteReader();
                //SelectStatus = SelectStatus;
                if (SelectStatus == 0)
                {
                    if (rdr.Read())
                    {
                        info.UserAutoID = rdr[0].ToString();
                        info.UserFirstNamea = rdr[1].ToString();
                        info.ProfileID = rdr[2].ToString();
                        info.Age = rdr[3].ToString();
                        info.Height = rdr[4].ToString();
                        info.Religion = rdr[5].ToString();
                        info.Education = rdr[6].ToString();
                        info.Community = rdr[7].ToString();
                        info.MaritalStatus = rdr[8].ToString();
                        info.FamilyLocation = rdr[9].ToString();
                        info.Occupation = rdr[10].ToString();
                        info.ProfilePicPath = rdr[11].ToString();
                        info.InterestStatus = rdr[12].ToString();
                    }
                }
            }
            catch
            {

            }
            finally
            {
                rdr.Close();
                sqlcon.Close();

            }
            return info;

What I have tried:

You can see above code to understand what I have to trying..

解决方案

You need to define the output parameter on your cmd object e.g.

cmd.Parameters.Add("@SelectStatus", SqlDbType.VarChar);
cmd.Parameters["@SelectStatus"].Direction = ParameterDirection.Output;

Then after you have called the SP you can use the approach suggested in Solution 1, but as you are returning an integer you should use

(int)Command.Parameters["@SelectStatus"].Value;

string retunvalue = (string)cmd.Parameters["@SelectStatus"].Value;

这篇关于如何从SQL存储过程中获取返回值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！