不使用游标的每行的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

Marc的回答很好（如果可以解决的话，我会对此发表评论！）

只是以为我指出，更改循环可能会更好，因此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