如何使用SQL Server将存储过程的结果存储在变量中

I am working on a SQL query where I have to store result of a stored procedure into a string type variable. There is a stored procedure named SP_CheckAgentProperty which is returning a string type of value either 'y' or 'N' or 'NP'.

我正在进行SQL查询，我必须将存储过程的结果存储到字符串类型变量中。有一个名为SP_CheckAgentProperty的存储过程，它返回一个字符串类型的值'y'或'N'或'NP'。

I am passing an integer value to the stored procedure. I want to store this output in any string variable. For this I am using this SQL query:

我将一个整数值传递给存储过程。我想将此输出存储在任何字符串变量中。为此，我使用此SQL查询：

My stored procedure is:

我的存储过程是：

CREATE Procedure [dbo].[SP_CheckAgentProperty] --12
(
    @ID bigint =null
)      
As
BEGIN
------Calculating total Ads Post allowed of any specific package of any user-----
DECLARE @Ad int=(SELECT tblPackage.Ads FROM tblPayment_Details INNER JOIN tblPayments ON tblPayments.ID = 
            tblPayment_Details.Payment_ID INNER JOIN tblPackage ON tblPayments.Package_ID = tblPackage.ID
            WHERE (tblPayment_Details.Payment_ID =(SELECT MAX(ID) AS d  FROM tblPayments AS tblPayments_1 WHERE (User_ID = @ID))))
            print @Ad
------Calculating the date when the user makes the last payment------
DECLARE @St DATE=(SELECT tblPayment_Details.Date FROM tblPayment_Details INNER JOIN tblPayments ON 
            tblPayments.ID = tblPayment_Details.Payment_ID INNER JOIN tblPackage ON tblPayments.Package_ID = tblPackage.ID
            WHERE (tblPayment_Details.Payment_ID =(SELECT MAX(ID) AS d  FROM tblPayments AS tblPayments_1 WHERE (User_ID = @ID))))
            print @St
------Calculating the validity of specific package taken by any user-----
DECLARE @LT int=(SELECT tblPackage.Validity FROM tblPayment_Details INNER JOIN tblPayments ON tblPayments.ID = 
            tblPayment_Details.Payment_ID INNER JOIN tblPackage ON tblPayments.Package_ID = tblPackage.ID
            WHERE (tblPayment_Details.Payment_ID =(SELECT MAX(ID) AS d  FROM tblPayments AS tblPayments_1 WHERE (User_ID = @ID))))
            print @LT
print dateadd(DAY,@LT,@St)
-------Calculating the Remaining days of package taken by the user
DECLARE @NoOfDays int=(select DATEDIFF(DAY,GETDATE(),dateadd(DAY,@LT,@St)))
print @NoOfDays 
-------Calculating if the user makes does not any payment in history------
DECLARE @SS int=(ISNULL(DATEDIFF(DAY, GETDATE(), @St), 0))
IF(@SS='0')
BEGIN
    select 'NP' as Message
END
ELSE
BEGIN
    if(@NoOfDays<=0)
    BEGIN
        --select 'This User Does Not Make a Payment.' as Message
        select 'MP' as Message
    END
    ELSE
    BEGIN
        DECLARE @TOT int=(select count(*) from tblProperty where tblProperty.Date between @St and dateadd(DAY,@LT,@St)) 
        --group by tblProperty.ID
        --select count(*) from tblProperty where tblProperty.Date between '2015-07-04' and dateadd(DAY,20,'2015-07-04')
        IF(@TOT<@Ad)
        BEGIN
            select 'y' as Message
        END
        ELSE
        BEGIN
            select 'N' as Message
        END
    END
END
END

And I am using the above stored procedure like this:

我正在使用上面这样的存储过程：

declare @ss varchar(10)

exec @ss = SP_CheckAgentProperty 10

if(@ss='NP')
BEGIN
    print 'Not Payment'
END
else
BEGIN
  print 'Payment'
END

The above query is returning the appropriate result but when I am using its output in if condition then it is not working.

上面的查询返回了相应的结果，但是当我在if条件下使用它的输出时，它不起作用。

1 个解决方案

#1

If the procedure is "returning" the value by selecting it, you'll have to use insert into with something like this:

如果该过程通过选择它“返回”该值，则必须使用insert into，如下所示：

declare @ss table (ss varchar(10))

insert into @ss exec SP_CheckAgentProperty 10

if(exists (select 1 from @ss where ss='NP') ...

or if it has an output parameter, then the call should be:

或者如果它有输出参数，那么调用应该是：

declare @ss varchar(10)

exec SP_CheckAgentProperty 10, @ss output

if(@ss='NP')

#1