我可以在T-SQL中循环一个表变量吗？ [英] Can I loop through a table variable in T-SQL?

问题描述

有没有在T-SQL中循环表变量？

Is there anyway to loop through a table variable in T-SQL?

DECLARE @table1 TABLE ( col1 int )  
INSERT into @table1 SELECT col1 FROM table2

我也使用游标，比表变量更灵活。

I use cursors as well, but cursors seem less flexible than table variables.

DECLARE cursor1 CURSOR  
    FOR SELECT col1 FROM table2  
OPEN cursor1  
FETCH NEXT FROM cursor1

我想能够以同样的方式使用表变量光标。这样我可以在过程的一部分中对表变量执行一些查询，然后稍后为表变量中的每一行执行一些代码。

I would like to be able to use a table variable in the same manner as a cursor. That way I could execute some query on the table variable in one part of the procedure, and then later execute some code for each row in the table variable.

任何帮助非常感谢。

推荐答案

向表变量添加标识，并轻松从1到@@ ROWCOUNT INSERT-SELECT。

Add an identity to your table variable, and do an easy loop from 1 to the @@ROWCOUNT of the INSERT-SELECT.

尝试：

DECLARE @RowsToProcess  int
DECLARE @CurrentRow     int
DECLARE @SelectCol1     int

DECLARE @table1 TABLE (RowID int not null primary key identity(1,1), col1 int )  
INSERT into @table1 (col1) SELECT col1 FROM table2
SET @RowsToProcess=@@ROWCOUNT

SET @CurrentRow=0
WHILE @CurrentRow<@RowsToProcess
BEGIN
    SET @CurrentRow=@CurrentRow+1
    SELECT 
        @SelectCol1=col1
        FROM @table1
        WHERE RowID=@CurrentRow

    --do your thing here--

END

这篇关于我可以在T-SQL中循环一个表变量吗？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！