如何迭代存储过程中的记录集? [英] How can I iterate over a recordset within a stored procedure?
问题描述
我需要从存储过程迭代一个记录集,并使用每个字段作为参数执行另一个存储过程。我无法在代码中完成此迭代。我在互联网上找到了样本,但它们似乎都在处理一个计数器。我不确定我的问题是否涉及柜台。我需要T-SQL等效于 foreach
I need to iterate over a recordset from a stored procedure and execute another stored procedure using each fields as arguments. I can't complete this iteration in the code. I have found samples on the internets, but they all seem to deal with a counter. I'm not sure if my problem involved a counter. I need the T-SQL equivalent of a foreach
目前,我的第一个存储过程将其记录集存储在临时表#mytemp中。我假设我会像这样调用辅助存储过程:
Currently, my first stored procedure stores its recordset in a temp table, #mytemp. I assume I will call the secondary stored procedure like this:
while (something)
execute nameofstoredprocedure arg1, arg2, arg3
end
推荐答案
你需要创建一个光标循环遍历记录集。
You need to create a cursor to loop through the record set.
示例表:
CREATE TABLE Customers
(
CustomerId INT NOT NULL PRIMARY KEY IDENTITY(1,1)
,FirstName Varchar(50)
,LastName VARCHAR(40)
)
INSERT INTO Customers VALUES('jane', 'doe')
INSERT INTO Customers VALUES('bob', 'smith')
光标:
DECLARE @CustomerId INT, @FirstName VARCHAR(30), @LastName VARCHAR(50)
DECLARE @MessageOutput VARCHAR(100)
DECLARE Customer_Cursor CURSOR FOR
SELECT CustomerId, FirstName, LastName FROM Customers
OPEN Customer_Cursor
FETCH NEXT FROM Customer_Cursor INTO
@CustomerId, @FirstName, @LastName
WHILE @@FETCH_STATUS = 0
BEGIN
SET @MessageOutput = @FirstName + ' ' + @LastName
RAISERROR(@MessageOutput,0,1) WITH NOWAIT
FETCH NEXT FROM Customer_Cursor INTO
@CustomerId, @FirstName, @LastName
END
CLOSE Customer_Cursor
DEALLOCATE Customer_Cursor
这里是MSDN如何创建它们的链接。
Here is a link to MSDN on how to create them.
http:// msdn.microsoft.com/en-us/library/ms180169.aspx
这就是我使用Raise Error而不是PRINT输出的原因。
http:// structuredsight.com/2014/11/24/wait-wait-dont-tell-me-on-second-thought/
This is why I used Raise Error instead of PRINT for output.
http://structuredsight.com/2014/11/24/wait-wait-dont-tell-me-on-second-thought/
这篇关于如何迭代存储过程中的记录集?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!