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

2021-12-20 00:00:00 sql sql-server stored-procedures cursor

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

How can one call a stored procedure for each row in a table, where the columns of a row are input parameters to the sp without using a Cursor?

推荐答案

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

Generally speaking I always look for a set based approach (sometimes at the expense of changing the schema).

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

However, this snippet does have its place..

-- 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

相关文章