如何使用 SQL Server 将存储过程的结果存储在变量中 [英] How to store result of stored procedure in a variable using SQL Server

问题描述

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

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 查询:

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:

我的存储过程是:

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

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

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

推荐答案

如果该过程通过选择它来返回"值，则您必须使用 insert into 如下所示:

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

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

这篇关于如何使用 SQL Server 将存储过程的结果存储在变量中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！