如何在存储过程中迭代记录集？

Question

我需要遍历一个存储过程的记录集并使用每个字段作为参数执行另一个存储过程。我无法在代码中完成这个迭代。我在互联网上找到了一些示例，但它们似乎都涉及计数器。我不确定我的问题是否涉及计数器。我需要一个T-SQL版本的foreach循环。

目前，我的第一个存储过程将其记录集存储在临时表#mytemp中。我假设我会这样调用次要存储过程：

while (something)
    execute nameofstoredprocedure arg1, arg2, arg3
end

- David Fox

Answer 1

你需要创建一个游标来循环遍历记录集。

示例表格：

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链接。

http://msdn.microsoft.com/en-us/library/ms180169.aspx

这就是为什么我使用Raise Error而不是PRINT来输出。
https://kemiller2002.github.io/2014/11/24/Wait-Wait-Don-t-Tell-Me-On-Second-Thought.html

- kemiller2002

---

我已经成功将eggheadcafe.com的链接移植到我的代码中。谢谢。 - David Fox

这篇 egghead 文章真是太好了！ - Phil Nicholas

此答案已被标记为需要删除，因为它只是一个链接，而且该链接已经失效。请您能否扩展答案，完整回答问题，而不需要读者点击链接？ - josliber

你应该避免使用游标。有许多无需游标的方法，甚至包括下面这个。 - Piotr

@kemiller2002 我得到了“语法错误：意外的'@CustomerId'（在文本后缀处）” - PouncingPoodle

Answer 2

在SQL存储过程中，循环遍历行非常容易。您只需要使用游标即可。以下是一个例子：

让我们考虑一张表 Employee，其中包含NAME和AGE两列，其中有50条记录，您需要执行一个名为TESTPROC的存储过程，该过程将获取每行的姓名和年龄参数。

create procedure CursorProc
as
begin
   declare @count bigint;
   declare @age varchar(500)
   declare @name varchar(500)
   select @count = (select count(*) from employee)
   declare FirstCursor cursor for select name, age from employee
   open FirstCursor 
   while @count > 0
      begin
         fetch FirstCursor into @name, @age
         Exec TestProc @name, @age
         set @count = @count - 1
      end
   close FirstCursor 
   deallocate FirstCursor 
end

确保您在使用完游标后进行回收以避免错误。

- ahmar

---

你救了我的一天。这是我第一个存储过程 :) - rajugaadu

Answer 3

尝试使用以下代码（无需游标循环）：

CREATE TABLE #Results  (RowID  int identity(1,1), Col1  varchar(5), Col2 int, ... )
DECLARE @Current  int
       ,@End      int
DECLARE @Col1  varchar(5)
       ,@Col2 int
       ,...

--you need to capture the result set from the primary stored procedure
INSERT INTO #Results
    (Col1, COl2,...)
    EXEC nameofstoredprocedure_1 arg1, arg2, arg3
SELECT @End=@@ROWCOUNT,@Current=0

--process each row in the result set
WHILE @Current<@End
BEGIN
    SET @Current=@Current+1

    SELECT
        @Col1=COl1, @Col2=Col2
        FROM #Results
        WHERE RowID=@Current

    --call the secondary procedure for each row
    EXEC nameofstoredprocedure_2  @Col1, @Col2,...

END

工作示例：

CREATE PROCEDURE nameofstoredprocedure_1
(@arg1 int, @arg2 int, @arg3 int)
AS
SELECT 'AAA',@arg1 UNION SELECT 'BBB',@arg2 UNION SELECT 'CCC',@arg3
GO

CREATE PROCEDURE nameofstoredprocedure_2
(@P1 varchar(5), @P2 int)
AS
PRINT '>>'+ISNULL(@P1,'')+','+ISNULL(CONVERT(varchar(10),@P2),'')
GO

CREATE TABLE #Results  (RowID  int identity(1,1), Col1  varchar(5), Col2 int)
DECLARE @Current  int
       ,@End      int
DECLARE @Col1  varchar(5)
       ,@Col2 int


INSERT INTO #Results
    (Col1, COl2)
    EXEC nameofstoredprocedure_1 111, 222, 333
SELECT @End=@@ROWCOUNT,@Current=0

WHILE @Current<@End
BEGIN
    SET @Current=@Current+1

    SELECT
        @Col1=COl1, @Col2=Col2
        FROM #Results
        WHERE RowID=@Current

    EXEC nameofstoredprocedure_2  @Col1, @Col2

END

输出：

(3 row(s) affected)
>>AAA,111
>>BBB,222
>>CCC,333

- KM.

---

当我尝试在我的代码中使用这个时，循环没有执行21条记录在#mytemp。 - David Fox

在 SELECT @End=@@ROWCOUNT,@Current=0 和 WHILE @Current<@End 之间添加以下内容：print 'will process '+ISNULL(CONVERT(varchar(5),@End),'')+' rows'，并在 SET @Current=@Current+1 和 SELECT... 之间添加以下内容：print ' processing row '+ISNULL(CONVERT(varchar(5),@Current),'')。此外，如果您没有使用插入填充 #Results 并同时运行 WHILE，则可以在 while 之前添加以下内容：SELECT @End=COUNT(*) FROM #Results。 - KM.

游标不好，发表你对我的回答的修改，我会指出你的错误。在使用游标之前，我宁愿剜掉自己的眼睛。 - KM.