不使用游标的每一行的 SQL 调用存储过程
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1656804/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL Call Stored Procedure for each Row without using a cursor
提问by Johannes Rudolph
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 withoutusing a Cursor?
如何在不使用 Cursor 的情况下为表中的每一行调用存储过程，其中行的列是 sp 的输入参数？
回答by Mark Powell
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
回答by marc_s
You could do something like this: order your table by e.g. CustomerID (using the AdventureWorks Sales.Customersample table), and iterate over those customers using a WHILE loop:
您可以执行以下操作：通过例如 CustomerID（使用 AdventureWorksSales.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
That should work with any table as long as you can define some kind of an ORDER BYon some column.
只要您可以ORDER BY在某些列上定义某种类型，这应该适用于任何表。
回答by Thomas Gabriel
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)
Ok, so I would never put such code into production, but it does satisfy your requirements.
好的，所以我永远不会将这样的代码投入生产，但它确实满足您的要求。
回答by Maxxx
Marc's answer is good (I'd comment on it if I could work out how to!)
Just thought I'd point out that it may be better to change the loop so the SELECTonly exists once (in a real case where I needed to do this, the SELECTwas quite complex, and writing it twice was a risky maintenance issue).
马克的回答很好（如果我能弄清楚如何做，我会评论它！）
只是想我会指出改变循环可能会更好，所以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
回答by David Griffiths
If you can turn the stored procedure into a function that returns a table, then you can use cross-apply.
如果可以将存储过程变成返回表的函数，则可以使用交叉应用。
For example, say you have a table of customers, and you want to compute the sum of their orders, you would create a function that took a CustomerID and returned the sum.
例如，假设您有一个客户表，并且您想计算他们的订单总和，您将创建一个函数，该函数采用 CustomerID 并返回总和。
And you could do this:
你可以这样做：
SELECT CustomerID, CustomerSum.Total
FROM Customers
CROSS APPLY ufn_ComputeCustomerTotal(Customers.CustomerID) AS CustomerSum
Where the function would look like:
函数看起来像：
CREATE FUNCTION ComputeCustomerTotal
(
@CustomerID INT
)
RETURNS TABLE
AS
RETURN
(
SELECT SUM(CustomerOrder.Amount) AS Total FROM CustomerOrder WHERE CustomerID = @CustomerID
)
Obviously, the example above could be done without a user defined function in a single query.
显然，上面的示例可以在单个查询中没有用户定义的函数的情况下完成。
The drawback is that functions are very limited - many of the features of a stored procedure are not available in a user-defined function, and converting a stored procedure to a function does not always work.
缺点是函数非常有限 - 存储过程的许多功能在用户定义的函数中不可用，并且将存储过程转换为函数并不总是有效。
回答by Mitch Wheat
For SQL Server 2005 onwards, you can do this with CROSS APPLYand a table-valued function.
对于 SQL Server 2005 以后的版本，您可以使用CROSS APPLY和表值函数来完成此操作。
Just for clarity, I'm referring to those cases where the stored procedure can be converted into a table valued function.
为了清楚起见，我指的是存储过程可以转换为表值函数的情况。
回答by AjV Jsy
I'd use the accepted answer, but another possibility is to use a table variable to hold a numbered set of values (in this case just the ID field of a table) and loop through those by Row Number with a JOIN to the table to retrieve whatever you need for the action within the loop.
我会使用接受的答案，但另一种可能性是使用表变量来保存一组编号的值（在这种情况下只是表的 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
回答by beruic
This is a variation of n3rds solution above. No sorting by using ORDER BY is needed, as MIN() is used.
这是上述 n3rds 解决方案的变体。不需要使用 ORDER BY 进行排序，因为使用了 MIN()。
Remember that CustomerID (or whatever other numerical column you use for progress) must have a unique constraint. Furthermore, to make it as fast as possible CustomerID must be indexed on.
请记住 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
I use this approach on some varchars I need to look over, by putting them in a temporary table first, to give them an ID.
我在一些需要查看的 varchars 上使用这种方法，首先将它们放在临时表中，为它们提供 ID。
回答by Dani
If you don't what to use a cursor I think you'll have to do it externally (get the table, and then run for each statement and each time call the sp) it Is the same as using a cursor, but only outside SQL. Why won't you use a cursor ?
如果您不知道如何使用游标，我认为您必须在外部进行（获取表，然后为每个语句运行并每次调用 sp）它与使用游标相同，但仅限于外部SQL。为什么不使用游标？
回答by Jonas Stensved
I usually do it this way when it's a quite a few rows:
当它有很多行时，我通常会这样做：
- Select all sproc parameters in a dataset with SQL Management Studio
- Right-click -> Copy
- Paste in to excel
- Create single-row sql statements with a formula like '="EXEC schema.mysproc @param=" & A2' in a new excel column. (Where A2 is your excel column containing the parameter)
- Copy the list of excel statements into a new query in SQL Management Studio and execute.
- Done.
- 使用 SQL Management Studio 选择数据集中的所有 sproc 参数
- 右键单击 -> 复制
- 粘贴到excel
- 在新的 excel 列中使用类似 '="EXEC schema.mysproc @param=" & A2' 的公式创建单行 sql 语句。（其中 A2 是包含参数的 excel 列）
- 将 excel 语句列表复制到 SQL Management Studio 中的新查询中并执行。
- 完毕。
(On larger datasets i'd use one of the solutions mentioned above though).
（在较大的数据集上，我会使用上面提到的解决方案之一）。
