T-SQL 循环查询结果 [英] T-SQL loop over query results

问题描述

我运行一个查询 select @id=table.id from table 并且我需要遍历结果以便我可以为每一行执行一个存储过程 exec stored_proc @varName=@id,@otherVarName='test'

I run a query select @id=table.id from table and I need to loop over the results so I can exec a store procedure for each row exec stored_proc @varName=@id,@otherVarName='test'

如何在 T-SQL 脚本中执行此操作?

How can I do this in a T-SQL script?

推荐答案

在这种情况下，您可以使用 CURSOR:

You could use a CURSOR in this case:

DECLARE @id INT
DECLARE @name NVARCHAR(100)
DECLARE @getid CURSOR

SET @getid = CURSOR FOR
SELECT table.id,
       table.name
FROM   table

OPEN @getid
FETCH NEXT
FROM @getid INTO @id, @name
WHILE @@FETCH_STATUS = 0
BEGIN
    EXEC stored_proc @varName=@id, @otherVarName='test', @varForName=@name
    FETCH NEXT
    FROM @getid INTO @id, @name
END

CLOSE @getid
DEALLOCATE @getid

修改为显示表格中的多个参数.

这篇关于T-SQL 循环查询结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！