SQL Server stored procedure store multiple rows of SELECT statement result into single variable

Question

I have a query with a SELECT statement that will return 2 or more rows as a result. How can I store these rows of data into a variable? Because I need the variable to check whether any of the rows is empty/null. How can I achieve this?

So far I've done this:

BEGIN
    SELECT 
        @AINum = ISNULL(so.U_SI7_DPDocNum, 0), @soDocNum = so.DocNum
    FROM  
        DLN1 doline 
    INNER JOIN 
        ORDR so ON doline.BaseRef = so.DocNum
    WHERE 
        doline.DocEntry = @docEntry

    WHILE(@AINum IS NOT NULL)
    BEGIN
        IF(@AINum <= 0)
        BEGIN
            SELECT @errCode = 003;
            RETURN;
        END
    END
END

UPDATED query using EXISTS

SELECT @errCode = 003
WHERE NOT EXISTS (SELECT so.U_SI7_DPDocNum
                  FROM DLN1 doline 
                  INNER JOIN ORDR so ON doline.BaseRef = so.DocNum
                  WHERE doline.DocEntry = @docEntry)
RETURN;

The @AINum will have to store multiple rows of data from the SELECT statement result. @errCode is an output variable.

Thank you.

@MitchWheat can the variable inside the table being used in while condition and null checking? Because it gaves me error when I want to use it in the while loop. — Sivvie Lim
– Sivvie Lim, Commented Oct 2, 2018 at 0:57
Tsql should not be procedural. You probably don't need a while loop. Look at Exists and 'Not Exists' — granadaCoder
– granadaCoder, Commented Oct 2, 2018 at 1:00
@granadaCoder I've updated the question with Exists clause, could you help me take a look on it? Thanks! — Sivvie Lim
– Sivvie Lim, Commented Oct 2, 2018 at 1:29
@Squirrel If the query return more than 2 rows and one of the U_SI7_DPDocNum is NULL, then pops up the errCode on the screen. — Sivvie Lim
– Sivvie Lim, Commented Oct 2, 2018 at 2:20

Squirrel · Accepted Answer · 2018-10-02 02:23:59Z

1

-- initialize to 0
SELECT  @errCode = 0;

-- assign value of 003 if it the DPDocNum is NULL or < 0
SELECT  @errCode = 003
FROM    DLN1 doline 
INNER JOIN ORDR so ON doline.BaseRef = so.DocNum
WHERE   doline.DocEntry = @docEntry
AND     (so.U_SI7_DPDocNum IS NULL OR so.U_SI7_DPDocNum <= 0)

answered Oct 2, 2018 at 2:23

Squirrel

24.8k5 gold badges41 silver badges37 bronze badges

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

Collectives™ on Stack Overflow

SQL Server stored procedure store multiple rows of SELECT statement result into single variable

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related