我可以在T-SQL中循环一个表变量吗? [英] Can I loop through a table variable in T-SQL?
本文介绍了我可以在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屋!
查看全文