一般来说，我总是在寻找一种基于集合的方法（有时以更改架构为代价）。但是，此片段确实有它的位置。-- Declare & init (2008 syntax)DECLARE @CustomerID INT = 0-- Iterate over all customersWHILE (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 @CustomerIdEND
您可以执行以下操作：通过例如CustomerID（使用AdventureWorks Sales.Customer示例表）订购表，并使用WHILE循环遍历那些客户：-- define the last customer ID handledDECLARE @LastCustomerID INTSET @LastCustomerID = 0-- define the customer ID to be handled nowDECLARE @CustomerIDToHandle INT-- select the next customer to handle SELECT TOP 1 @CustomerIDToHandle = CustomerIDFROM Sales.CustomerWHERE CustomerID > @LastCustomerIDORDER BY CustomerID-- as long as we have customers...... WHILE @CustomerIDToHandle IS NOT NULLBEGIN -- 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 CustomerIDEND只要您可以ORDER BY在某些列上定义某种形式的表，那么该表就适用于任何表。
Marc的回答很好（如果可以解决的话，我会对此发表评论！）只是以为我指出，更改循环可能会更好，因此SELECT仅存在一次（在实际情况下，我需要这样做SELECT非常复杂，将其写入两次是有风险的维护问题）。-- define the last customer ID handledDECLARE @LastCustomerID INTSET @LastCustomerID = 0-- define the customer ID to be handled nowDECLARE @CustomerIDToHandle INTSET @CustomerIDToHandle = 1-- as long as we have customers...... WHILE @LastCustomerID <> @CustomerIDToHandleBEGIN 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