How to store result of stored procedure in a variable using SQL Server

Question

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

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:

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.

Answer 1

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