不使用游标为每行调用 SQL 存储过程

Question

如何在不使用游标的情况下为表中的每一行调用存储过程，其中行的列是 sp 的输入参数？

Answer 1

一般来说，我总是寻找基于集合的方法（有时以更改架构为代价）。

然而，这个片段确实有它的位置..

-- Declare & init (2008 syntax)
DECLARE @CustomerID INT = 0

-- Iterate over all customers
WHILE (1 = 1) 
BEGIN  

  -- Get next customerId
  SELECT TOP 1 @CustomerID = CustomerID
  FROM Sales.Customer
  WHERE CustomerID > @CustomerId 
  ORDER BY CustomerID

  -- Exit loop if no more customers
  IF @@ROWCOUNT = 0 BREAK;

  -- call your sproc
  EXEC dbo.YOURSPROC @CustomerId

END

Answer 2

您可以执行以下操作：按 CustomerID 等对表进行排序（使用 AdventureWorks Sales.Customer 示例表），并使用 WHILE 循环迭代这些客户：

-- define the last customer ID handled
DECLARE @LastCustomerID INT
SET @LastCustomerID = 0

-- define the customer ID to be handled now
DECLARE @CustomerIDToHandle INT

-- select the next customer to handle    
SELECT TOP 1 @CustomerIDToHandle = CustomerID
FROM Sales.Customer
WHERE CustomerID > @LastCustomerID
ORDER BY CustomerID

-- as long as we have customers......    
WHILE @CustomerIDToHandle IS NOT NULL
BEGIN
    -- call your sproc

    -- set the last customer handled to the one we just handled
    SET @LastCustomerID = @CustomerIDToHandle
    SET @CustomerIDToHandle = NULL

    -- select the next customer to handle    
    SELECT TOP 1 @CustomerIDToHandle = CustomerID
    FROM Sales.Customer
    WHERE CustomerID > @LastCustomerID
    ORDER BY CustomerID
END

只要满足以下条件，就应该适用于任何表：您可以在某些列上定义某种ORDER BY。

Answer 3

DECLARE @SQL varchar(max)=''

-- MyTable has fields fld1 & fld2

Select @SQL = @SQL + 'exec myproc ' + convert(varchar(10),fld1) + ',' 
                   + convert(varchar(10),fld2) + ';'
From MyTable

EXEC (@SQL)

好吧，所以我永远不会将这样的代码投入生产，但它确实满足您的要求。

Answer 4

我会使用已接受的答案，但另一种可能性是使用表变量来保存一组编号的值（在本例中只是表的 ID 字段），并按行号循环遍历这些值并与表连接以检索循环内操作所需的任何内容。

DECLARE @RowCnt int; SET @RowCnt = 0 -- Loop Counter

-- Use a table variable to hold numbered rows containg MyTable's ID values
DECLARE @tblLoop TABLE (RowNum int IDENTITY (1, 1) Primary key NOT NULL,
     ID INT )
INSERT INTO @tblLoop (ID)  SELECT ID FROM MyTable

  -- Vars to use within the loop
  DECLARE @Code NVarChar(10); DECLARE @Name NVarChar(100);

WHILE @RowCnt < (SELECT COUNT(RowNum) FROM @tblLoop)
BEGIN
    SET @RowCnt = @RowCnt + 1
    -- Do what you want here with the data stored in tblLoop for the given RowNum
    SELECT @Code=Code, @Name=LongName
      FROM MyTable INNER JOIN @tblLoop tL on MyTable.ID=tL.ID
      WHERE tl.RowNum=@RowCnt
    PRINT Convert(NVarChar(10),@RowCnt) +' '+ @Code +' '+ @Name
END

Answer 5

马克的回答很好（如果我能弄清楚如何做的话，我会对此发表评论！）
只是想我会指出，更改循环可能会更好，这样 SELECT 只存在一次（在我需要这样做的实际情况下，SELECT相当复杂，并且编写两次是一个有风险的维护问题）。

-- define the last customer ID handled
DECLARE @LastCustomerID INT
SET @LastCustomerID = 0
-- define the customer ID to be handled now
DECLARE @CustomerIDToHandle INT
SET @CustomerIDToHandle = 1

-- as long as we have customers......    
WHILE @LastCustomerID <> @CustomerIDToHandle
BEGIN  
  SET @LastCustomerId = @CustomerIDToHandle
  -- select the next customer to handle    
  SELECT TOP 1 @CustomerIDToHandle = CustomerID
  FROM Sales.Customer
  WHERE CustomerID > @LastCustomerId 
  ORDER BY CustomerID

  IF @CustomerIDToHandle <> @LastCustomerID
  BEGIN
      -- call your sproc
  END

END

Answer 6

如果可以将存储过程变成返回表的函数，那么就可以使用交叉应用。

例如，假设您有一个客户表，并且您想要计算其订单的总和，您将创建一个接受 CustomerID 并返回总和的函数。

您可以这样做：

SELECT CustomerID, CustomerSum.Total

FROM Customers
CROSS APPLY ufn_ComputeCustomerTotal(Customers.CustomerID) AS CustomerSum

函数如下所示：

CREATE FUNCTION ComputeCustomerTotal
(
    @CustomerID INT
)
RETURNS TABLE
AS
RETURN
(
    SELECT SUM(CustomerOrder.Amount) AS Total FROM CustomerOrder WHERE CustomerID = @CustomerID
)

显然，上面的示例可以在单个查询中无需用户定义的函数来完成。

缺点是函数非常有限——存储过程的许多功能在用户定义的函数中不可用，并且将存储过程转换为函数并不总是有效。

Answer 7

对于 SQL Server 2005 及以上版本，您可以使用 CROSS APPLY 执行此操作表值函数。

• 在 SQL Server 2005 中使用 CROSS APPLY

为了清楚起见，我指的是存储过程可以转换为表值函数的情况。

Answer 8

这是已提供答案的变体，但性能应该更好，因为它不需要 ORDER BY、COUNT 或 MIN/MAX。这种方法的唯一缺点是您必须创建一个临时表来保存所有 Id（假设您的 CustomerID 列表中有间隙）。

也就是说，我同意@Mark Powell 的观点，尽管一般来说，基于集合的方法应该仍然更好。

DECLARE @tmp table (Id INT IDENTITY(1,1) PRIMARY KEY NOT NULL, CustomerID INT NOT NULL)
DECLARE @CustomerId INT 
DECLARE @Id INT = 0

INSERT INTO @tmp SELECT CustomerId FROM Sales.Customer

WHILE (1=1)
BEGIN
    SELECT @CustomerId = CustomerId, @Id = Id
    FROM @tmp
    WHERE Id = @Id + 1

    IF @@rowcount = 0 BREAK;

    -- call your sproc
    EXEC dbo.YOURSPROC @CustomerId;
END

Answer 9

这是上述 n3rds 解决方案的变体。不需要使用 ORDER BY 进行排序，因为使用了 MIN()。

请记住，CustomerID（或用于进度的任何其他数字列）必须具有唯一约束。此外，为了使其尽可能快，必须对 CustomerID 建立索引。

-- Declare & init
DECLARE @CustomerID INT = (SELECT MIN(CustomerID) FROM Sales.Customer); -- First ID
DECLARE @Data1 VARCHAR(200);
DECLARE @Data2 VARCHAR(200);

-- Iterate over all customers
WHILE @CustomerID IS NOT NULL
BEGIN  

  -- Get data based on ID
  SELECT @Data1 = Data1, @Data2 = Data2
    FROM Sales.Customer
    WHERE [ID] = @CustomerID ;

  -- call your sproc
  EXEC dbo.YOURSPROC @Data1, @Data2

  -- Get next customerId
  SELECT @CustomerID = MIN(CustomerID)
    FROM Sales.Customer
    WHERE CustomerID > @CustomerId 

END

我对一些需要查看的 varchar 使用这种方法，首先将它们放入临时表中，为它们提供一个 ID。

Answer 10

对此更好的解决方案是

1. 复制/粘贴存储过程的代码
2. 将该代码与您想要再次运行该代码的表（针对每一行）连接起来

这样您就可以获得干净的表格式输出。然而，如果对每一行运行 SP，每次迭代都会得到一个单独的查询结果，这很丑陋。

Answer 11

如果您不知道如何使用游标，我认为您必须在外部执行此操作（获取表，然后运行每个语句并每次调用 sp）
它与使用游标相同，但仅限于 SQL 之外。
为什么不使用光标？

Answer 12

当行数相当多时，我通常这样做：

1. ​​数据集中的所有 sproc 参数 -> 选择数据集中的所有 sproc 参数
2. 使用 SQL Management Studio右键单击 。复制
3. 粘贴到 Excel
4. 使用类似 '="EXEC schema.mysproc @param=" & 的公式创建单行 sql 语句新 Excel 列中的 A2'。 （其中 A2 是包含参数的 Excel 列）
5. 将 Excel 语句列表复制到 SQL Management Studio 中的新查询中并执行。
6. 完毕。

（不过，在较大的数据集上，我会使用上面提到的解决方案之一）。

Answer 13

分隔符 //

CREATE PROCEDURE setFakeUsers (OUT output VARCHAR(100))
BEGIN

    -- define the last customer ID handled
    DECLARE LastGameID INT;
    DECLARE CurrentGameID INT;
    DECLARE userID INT;

    SET @LastGameID = 0; 

    -- define the customer ID to be handled now

    SET @userID = 0;

    -- select the next game to handle    
    SELECT @CurrentGameID = id
    FROM online_games
    WHERE id > LastGameID
    ORDER BY id LIMIT 0,1;

    -- as long as we have customers......    
    WHILE (@CurrentGameID IS NOT NULL) 
    DO
        -- call your sproc

        -- set the last customer handled to the one we just handled
        SET @LastGameID = @CurrentGameID;
        SET @CurrentGameID = NULL;

        -- select the random bot
        SELECT @userID = userID
        FROM users
        WHERE FIND_IN_SET('bot',baseInfo)
        ORDER BY RAND() LIMIT 0,1;

        -- update the game
        UPDATE online_games SET userID = @userID WHERE id = @CurrentGameID;

        -- select the next game to handle    
        SELECT @CurrentGameID = id
         FROM online_games
         WHERE id > LastGameID
         ORDER BY id LIMIT 0,1;
    END WHILE;
    SET output = "done";
END;//

CALL setFakeUsers(@status);
SELECT @status;

Answer 14

我遇到过需要对结果集（表）执行一系列操作的情况。这些操作都是集合操作，所以这不是问题，但是......
我需要在多个地方执行此操作。因此，将相关部分放入表类型中，然后使用每个结果集填充表变量，这样我就可以调用 sp 并在每次需要时重复操作。

虽然这没有解决他提出的确切问题，但它确实解决了如何在不使用游标的情况下对表的所有行执行操作。

@Johannes 没有深入了解他的动机，所以这可能对他有帮助，也可能没有帮助。

我的研究使我找到了这篇写得很好的文章，它作为我的解决方案的基础
https://codingsight.com/passing-data-table -as-parameter-to-stored-procedures/

这是设置，

    drop type if exists cpRootMapType 
go 

create type cpRootMapType as Table(
    RootId1 int 
    , RootId2 int
)

go 
drop procedure if exists spMapRoot2toRoot1
go 
create procedure spMapRoot2toRoot1
(
@map cpRootMapType Readonly
)
as

update linkTable set root = root1  
from linktable  lt 
join @map m on lt.root = root2

update comments set root = root1 
from comments c 
join @map m on c.root = root2

--  ever growing list of places this map would need to be applied....
--  now consolidated into one place 

这里是实现

... populate #matches

declare @map cpRootMapType 
insert @map select rootid1, rootid2 from #matches
exec spMapRoot2toRoot1 @map 

Answer 15

如果订单很重要

--declare counter
DECLARE     @CurrentRowNum BIGINT = 0;
--Iterate over all rows in [DataTable]
WHILE (1 = 1)
    BEGIN
        --Get next row by number of row
        SELECT TOP 1 @CurrentRowNum = extendedData.RowNum
                    --here also you can store another values
                    --for following usage
                    --@MyVariable = extendedData.Value
        FROM    (
                    SELECT 
                        data.*
                        ,ROW_NUMBER() OVER(ORDER BY (SELECT 0)) RowNum
                    FROM [DataTable] data
                ) extendedData
        WHERE extendedData.RowNum > @CurrentRowNum
        ORDER BY extendedData.RowNum

        --Exit loop if no more rows
        IF @@ROWCOUNT = 0 BREAK;

        --call your sproc
        --EXEC dbo.YOURSPROC @MyVariable
    END

Answer 16

我有一些生产代码，一次只能处理 20 名员工，下面是代码的框架。我刚刚复制了生产代码并删除了下面的内容。

ALTER procedure GetEmployees
    @ClientId varchar(50)
as
begin
    declare @EEList table (employeeId varchar(50));
    declare @EE20 table (employeeId varchar(50));

    insert into @EEList select employeeId from Employee where (ClientId = @ClientId);

    -- Do 20 at a time
    while (select count(*) from @EEList) > 0
    BEGIN
      insert into @EE20 select top 20 employeeId from @EEList;

      -- Call sp here

      delete @EEList where employeeId in (select employeeId from @EE20)
      delete @EE20;
    END;

  RETURN
end

Answer 17

我喜欢做与此类似的事情（尽管它仍然与使用光标非常相似）

[code]

-- Table variable to hold list of things that need looping
DECLARE @holdStuff TABLE ( 
    id INT IDENTITY(1,1) , 
    isIterated BIT DEFAULT 0 , 
    someInt INT ,
    someBool BIT ,
    otherStuff VARCHAR(200)
)

-- Populate your @holdStuff with... stuff
INSERT INTO @holdStuff ( 
    someInt ,
    someBool ,
    otherStuff
)
SELECT  
    1 , -- someInt - int
    1 , -- someBool - bit
    'I like turtles'  -- otherStuff - varchar(200)
UNION ALL
SELECT  
    42 , -- someInt - int
    0 , -- someBool - bit
    'something profound'  -- otherStuff - varchar(200)

-- Loop tracking variables
DECLARE @tableCount INT
SET     @tableCount = (SELECT COUNT(1) FROM [@holdStuff])

DECLARE @loopCount INT
SET     @loopCount = 1

-- While loop variables
DECLARE @id INT
DECLARE @someInt INT
DECLARE @someBool BIT
DECLARE @otherStuff VARCHAR(200)

-- Loop through item in @holdStuff
WHILE (@loopCount <= @tableCount)
    BEGIN

        -- Increment the loopCount variable
        SET @loopCount = @loopCount + 1

        -- Grab the top unprocessed record
        SELECT  TOP 1 
            @id = id ,
            @someInt = someInt ,
            @someBool = someBool ,
            @otherStuff = otherStuff
        FROM    @holdStuff
        WHERE   isIterated = 0

        -- Update the grabbed record to be iterated
        UPDATE  @holdAccounts
        SET     isIterated = 1
        WHERE   id = @id

        -- Execute your stored procedure
        EXEC someRandomSp @someInt, @someBool, @otherStuff

    END

[/code]

请注意，您不需要临时表上的标识或 isIterated 列/变量表，我只是更喜欢这样做，这样我就不必在迭代循环时从集合中删除顶部记录。