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.
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')
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments