CODEWITHSUNDEEP
sqlsql-server
Sivvie Lim
Sivvie Lim

Reputation: 1276

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

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.

Upvotes: 0

Views: 304

Answers (1)

Squirrel
Squirrel

Reputation: 24763

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

Upvotes: 1

Related Questions