SQL - Loop While grab value from a table and use the value in loop

Question

Table1:

SELECT * FROM Order WHERE OrderID = @OrderID

Basically, I want to run the above Select statement 5 times(1 for each OrderID in table1) using loop and UNION ALL the results into single result.

Answer 1

As I said in comments you can add a flag to #tmp table for example isChecked, instead of deleting rows from #tmp tables.

--Creating schema
DROP TABLE IF EXISTS tblOrder
DROP TABLE IF EXISTS #tblResult
DROP TABLE IF EXISTS #tmp

CREATE TABLE tblOrder (OrderId int) 
CREATE TABLE #tblResult (OrderId int) 

INSERT INTO tblOrder 
VALUES (1),
        (2),
        (3),
        (4),
        (5);

-- Tmp table to iterate over        
SELECT OrderId INTO #tmp
FROM tblOrder

DECLARE @tmpOrder int
--Main loop with required statements inside
WHILE EXISTS (SELECT TOP 1 1 FROM #tmp)
    BEGIN
        --Taking next Id and storing the result
        SELECT TOP 1 @tmpOrder = OrderId FROM #tmp

        INSERT INTO #tblResult
        SELECT * FROM  tblOrder WHERE OrderId = @tmpOrder
        --PRINT @tmpOrder

        --Remove the row that operation has been performed for
        -- You can use additional variable as a counter or a flag in #tmp table instead
        DELETE FROM #tmp 
        WHERE OrderId = @tmpOrder
    END

    SELECT * FROM #tblResult

In case it has to be union all I'd declare result as VARCHAR (max), assign empty string and then the loop will look like this:

DECLARE @sResult VARCHAR (max) = ''
WHILE EXISTS (SELECT TOP 1 1 FROM #tmp)
    BEGIN
        --Taking next Id and storing the result
        SELECT TOP 1 @tmpOrder = OrderId FROM #tmp

        SET @sResult += 'SELECT * FROM tblOrder WHERE OrderId = '+CAST(@tmpOrder as VARCHAR(20))
        IF ((SELECT count(*) FROM #tmp) > 1 )
            BEGIN
                SET @sResult += ' UNION ALL '
            END
        --Remove the row that operation has been performed for
        -- You can use additional variable as a counter or a flag in #tmp table instead
        DELETE FROM #tmp 
        WHERE OrderId = @tmpOrder
    END

    EXEC (@sResult)

It is important to assign empty string to variable since adding to NULL will result in errors.

Let me know if this is what you were looking for.