SQL调用存储过程，不使用游标 [英] SQL Call Stored Procedure for each Row without using a cursor

问题描述

如何对表中的每一行调用存储过程，其中一行的列是输入参数sp 而不 使用Cursor？

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

这篇关于SQL调用存储过程，不使用游标的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！