How to set variable from stored procedure in query in sql server

Question

I want to get data from sp inside a query and set it to a variable. following is my query

WHILE ( ( SELECT COUNT(*) AS [COUNT]
          FROM ( SELECT TOP 1 tbl_PurchaseRequisitionOrOrder.AdditionalRefNumberPO
                 FROM dbo.tbl_PurchaseRequisitionOrOrder
                 WHERE tbl_PurchaseRequisitionOrOrder.AdditionalRefNumberPO IS NOT NULL
                 ORDER BY tbl_PurchaseRequisitionOrOrder.PROId DESC
               ) AS T122
        ) > 0 )
    DECLARE @rr VARCHAR(9)= ( SELECT TOP 1 tbl_PurchaseRequisitionOrOrder.AdditionalRefNumberPO
                              FROM dbo.tbl_PurchaseRequisitionOrOrder
                              WHERE tbl_PurchaseRequisitionOrOrder.AdditionalRefNumberPO IS NOT NULL
                              ORDER BY tbl_PurchaseRequisitionOrOrder.PROId DESC
                            );
          SET @rr= EXEC dbo.GetAddiRefNumberPO @rr

but i am getting Incorrect syntax near the keyword 'EXEC'.

on

 SET @rr= EXEC dbo.GetAddiRefNumberPO @rr

i am using sql server 2014 please help :)

EXEC dbo.GetAddiRefNumberPO @rr OUTPUT

artm
– artm

2016-10-13 11:46:30 +00:00
Commented Oct 13, 2016 at 11:46 — artm
– artm, Commented Oct 13, 2016 at 11:46

gofr1 · Accepted Answer · 2016-10-13 12:03:56Z

1

If you want to put data from SP into variable - you need to use OUTPUT:

ALTER PROCEDURE dbo.GetAddiRefNumberPO
    ...
    @rr VARCHAR(9) OUTPUT
AS
...

Then you can use it like:

DECLARE @rr VARCHAR(9);

EXECUTE dbo.GetAddiRefNumberPO ... @rr = @rr OUTPUT

SELECT @rr

In last select you will get value that was get from SP execution.

The main question is - You are using WHILE loop, so you got some @rr, then pass it to SP and try to write output from SP in that variable, but on next run you will get another @rr from your query. Maybe you need to use another variable? What is the purpose of this query?

answered Oct 13, 2016 at 12:03

gofr1

16k11 gold badges47 silver badges56 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Gordon Linoff · Accepted Answer · 2016-10-13 11:46:51Z

First, don't need to use COUNT() in your IF condition. Just use EXISTS.

Second, you need BEGIN/END:

WHILE (. . . )
BEGIN
    DECLARE @rr VARCHAR(MAX)= (SELECT TOP 1 o.AdditionalRefNumberPO
                               FROM dbo.tbl_PurchaseRequisitionOrOrder o
                               WHERE o.AdditionalRefNumberPO IS NOT NULL
                               ORDER BY o.PROId DESC);
     EXEC dbo.GetAddiRefNumberPO @rr ;
END;

Notes:

Stored procedures return integers, not strings. So, if you want the return value, you need to an integer variable, not a string one. But you are not using the retval, so I removed it.
Your error was cause because the WHILE was only declaring the variable @rr. The EXEC was the next statement after the WHILE loop.
I recommend that you always use BEGIN/END for any control statements, such as WHILE and IF. Now you know why.
Table aliases make queries easier to write and to read.

Collectives™ on Stack Overflow

How to set variable from stored procedure in query in sql server

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related