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

Sunny Sandeep

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.

James Z

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.

edited at
0

Comments

0 comments
Login to comment

Related

From Dev

Store the whole query result in variable using postgresql Stored procedure

From Dev

How to store query result in a variable in MySql stored Procedure

From Dev

How to store returned result using mysql stored procedure

From Dev

how to Call web service using stored procedure which returns json and store data in table using sql server?

From Dev

How to return the calculated (aggregated) result of a SQL Server stored procedure

From Dev

How to get sql server stored procedure result to process in an other stored procedure?

From Dev

SQL Server: using IF with as in stored procedure

From Dev

SQL Server 2008: Insert variable into DML statements using Stored Procedure

From Dev

SQL set variable as the result of a query in a stored procedure

From Dev

SQL set variable as the result of a query in a stored procedure

From Dev

T-SQL stored procedure result into variable

From Dev

How to check if a variable has a value in a SQL Server 2008 stored procedure

From Dev

How to return the output of stored procedure into a string variable in SQL Server

From Dev

Store a stored procedure result set in application variable error

From Dev

SQL Server Stored Procedure Using a Linked Server

From Dev

get the text of a stored procedure into a variable in SQL Server

From Dev

SQL Server Stored Procedure with a FileName variable

From Dev

SQL Server: calculate variable dates in stored procedure

From Dev

How to generate random passwords in SQL Server using stored procedure?

From Dev

How to get a SQL Server stored procedure returned rowset using pyodbc?

From Dev

How to access SQL Server using stored procedure in windows forms application?

From Dev

How invoke a Store Procedure using Hibernate con Sql Server 2000?

From Dev

Using insert stored procedure with SQL Server

From Dev

SQL Server stored procedure using INNER JOIN

From Dev

SQL Server signature on stored procedure using certificate

From Dev

Launching a SQL Server report using a stored procedure

From Dev

SQL Server signature on stored procedure using certificate

From Dev

SQL Server within & using stored procedure

From Dev

Launching a SQL Server report using a stored procedure

Related Related

  1. 1

    Store the whole query result in variable using postgresql Stored procedure

  2. 2

    How to store query result in a variable in MySql stored Procedure

  3. 3

    How to store returned result using mysql stored procedure

  4. 4

    how to Call web service using stored procedure which returns json and store data in table using sql server?

  5. 5

    How to return the calculated (aggregated) result of a SQL Server stored procedure

  6. 6

    How to get sql server stored procedure result to process in an other stored procedure?

  7. 7

    SQL Server: using IF with as in stored procedure

  8. 8

    SQL Server 2008: Insert variable into DML statements using Stored Procedure

  9. 9

    SQL set variable as the result of a query in a stored procedure

  10. 10

    SQL set variable as the result of a query in a stored procedure

  11. 11

    T-SQL stored procedure result into variable

  12. 12

    How to check if a variable has a value in a SQL Server 2008 stored procedure

  13. 13

    How to return the output of stored procedure into a string variable in SQL Server

  14. 14

    Store a stored procedure result set in application variable error

  15. 15

    SQL Server Stored Procedure Using a Linked Server

  16. 16

    get the text of a stored procedure into a variable in SQL Server

  17. 17

    SQL Server Stored Procedure with a FileName variable

  18. 18

    SQL Server: calculate variable dates in stored procedure

  19. 19

    How to generate random passwords in SQL Server using stored procedure?

  20. 20

    How to get a SQL Server stored procedure returned rowset using pyodbc?

  21. 21

    How to access SQL Server using stored procedure in windows forms application?

  22. 22

    How invoke a Store Procedure using Hibernate con Sql Server 2000?

  23. 23

    Using insert stored procedure with SQL Server

  24. 24

    SQL Server stored procedure using INNER JOIN

  25. 25

    SQL Server signature on stored procedure using certificate

  26. 26

    Launching a SQL Server report using a stored procedure

  27. 27

    SQL Server signature on stored procedure using certificate

  28. 28

    SQL Server within & using stored procedure

  29. 29

    Launching a SQL Server report using a stored procedure

HotTag

Archive