SQL-循环从表中获取值并在循环中使用值

正如我在评论中所说，您可以向#tmp表中添加一个标志（例如isChecked），而不是从#tmp表中删除行。

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

如果必须全部合并，我将结果声明为VARCHAR（max），则分配空字符串，然后循环将如下所示：

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)

将空字符串分配给变量很重要，因为添加到NULL会导致错误。

让我知道这是否是您想要的。