SQL 是否可以在不使用游标的情况下对集合执行存储过程？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/477064/
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
Is it possible to execute a stored procedure over a set without using a cursor?
提问by user58714
I would like to execute a stored procedure over each row in a set without using a cursor with something like this:
SELECT EXEC dbo.Sproc @Param1 = Table1.id
FROM Table1
I am using T-SQL in SQL Server 2005. I think this might be possible using a function, but I'd like to use a stored procedure if possible (company standards)
我想在集合中的每一行上执行一个存储过程，而不使用像这样的游标：
我在 SQL Server 2005 中使用 T-SQL。我认为这可能使用函数，但我想如果可能，使用存储过程（公司标准）SELECT EXEC dbo.Sproc @Param1 = Table1.id
FROM Table1
采纳答案by BankZ
9 out of 10 times you can do what you want without a cursor or a while loop. However, if you must use one, I have found that while loops tend to be faster.
10 次中有 9 次您可以在没有游标或 while 循环的情况下做您想做的事。但是，如果您必须使用一个，我发现 while 循环往往更快。
Also if you do not want to delete or update the table, you can use something like this:
此外，如果您不想删除或更新表，您可以使用以下内容：
DECLARE @id [type]
SELECT @id = MIN([id]) FROM [table]
WHILE @id IS NOT NULL
BEGIN
EXEC [sproc] @id
SELECT @id = MIN([id]) FROM [table] WHERE [id] > @id
END
回答by Josef
Yes. If you have a column you can use within the table to mark the ones processed, you can use WHILE EXISTS:
是的。如果您有可以在表中使用的列来标记已处理的列，则可以使用 WHILE EXISTS：
DECLARE @Id int
WHILE EXISTS(SELECT * FROM Table1 WHERE Processed='N')
BEGIN
SELECT Top 1 @Id = id from Table1 WHERE Procesed='N'
EXEC dbo.Sproc @Id
UPDATE Table1 SET Processed = 'Y' WHERE Id = @Id
END
Alternately, dump the ids into a temp table or table variable and delete when finished:
或者，将 id 转储到临时表或表变量中，并在完成后删除：
DECLARE @HoldTable table (Id int PRIMARY KEY)
DECLARE @Id int
INSERT INTO @HoldTable SELECT Id FROM Table1
WHILE EXISTS(SELECT * FROM @HoldTable)
BEGIN
SELECT @Id = id from @HoldTable
EXEC dbo.Sproc @Id
DELETE FROM @HoldTable where Id = @Id
END
回答by Joe Pineda
If you're only using SQL Server 2005 or newer, don't care about backwards compatibility and can convert your code to be in a User-Defined Function (rather than a stored proc) then you can use the new "CROSS APPLY" operator, which does use a syntax very similar to what you want. I found herea short intro (of course, you can also read the BOLs and MSDN)
如果您只使用 SQL Server 2005 或更高版本，不关心向后兼容性并且可以将您的代码转换为用户定义函数（而不是存储过程），那么您可以使用新的“CROSS APPLY”运算符，它使用的语法与您想要的非常相似。我在这里找到了一个简短的介绍（当然，您也可以阅读 BOL 和 MSDN）
Supposing your SP returns a single value named out_int, your example could be rewritten as:
假设您的 SP 返回一个名为out_int 的值，您的示例可以重写为：
SELECT T.id, UDF.out_int
FROM
Table1 T
CROSS APPLY
dbo.fn_My_UDF(T.id) AS UDF
This will retrieve each "id" from Table1 and use it to call fn_My_UDF, the result of which will appear in the final result-set besides the original parameter.
这将从 Table1 中检索每个“id”并使用它来调用 fn_My_UDF，其结果将出现在除了原始参数之外的最终结果集中。
A variat of "CROSS APPLY" is "OUTER APPLY". They are equivalents of "INNER JOIN" and "LEFT JOIN", but work on joining a table and a UDF (and calling the second at the same time).
“CROSS APPLY”的变体是“OUTER APPLY”。它们相当于“INNER JOIN”和“LEFT JOIN”，但用于连接表和 UDF（并同时调用第二个）。
If you must(by explicit order of the pointy-haired boss) use SPs insead, well - bad luck! You'll have to keep with cursors, or try cheating a bit: change the code into UDFs, and create wrapper SPs :D.
如果你必须（按照尖头发老板的明确命令）使用 SP，那么 - 运气不好！您将不得不继续使用游标，或者尝试作弊：将代码更改为 UDF，并创建包装 SP :D。
回答by HLGEM
Frankly if I needed to execute a stored proc for a set of data instead if the one record it was written for , I would write the set-based code instead of using the stored proc. This is the only efficient way to do this if you are going to be running against a large data set. You could go from hours to do the insert that the stored proc does to seconds or even milliseconds. Do not use record based processing ever when you need to processs a set of data especially not for a silly reason like reuse of code. Performance trumps reuse of code.
坦率地说，如果我需要为一组数据执行一个存储过程，而不是为它编写的一个记录，我会编写基于集合的代码，而不是使用存储过程。如果您要针对大型数据集运行，这是执行此操作的唯一有效方法。您可以从几个小时开始执行存储过程执行的插入到几秒甚至几毫秒的插入操作。当您需要处理一组数据时，不要使用基于记录的处理，尤其是不要出于诸如重用代码之类的愚蠢原因。性能胜过代码的重用。
回答by Joshua
If possible I'd write a second version of the stored proc that reads from a temp table.
如果可能，我会编写从临时表读取的存储过程的第二个版本。
If that's not possible than you're probably out of luck.
如果那是不可能的，那么您可能就不走运了。
回答by Weihui Guo
Take a look at this answerusing dynamic SQL. Here is what looks like based on your query.
使用动态 SQL看看这个答案。这是基于您的查询的样子。
DECLARE @TSQL NVARCHAR(MAX) = ''
SELECT @TSQL = @TSQL + N'EXEC dbo.Sproc ' + id + ';
' -- To insert a line break
FROM Table1
EXEC sp_executesql @TSQL
This equals to the following if you PRINT @TSQL. Note that how the line break is inserted.
如果您PRINT @TSQL. 请注意如何插入换行符。
EXEC dbo.Sproc id1;
EXEC dbo.Sproc id2;
EXEC dbo.Sproc id3;
...
If you don't need a stored procedure, it can be easier. I once had a need to update a table based on each row (Code is the column name, and [Value] the value) from a view. Because the view is dynamic and returns different rows each time, and hence different column-value pairs. I re-wrote the query without using any stored procedure.
如果您不需要存储过程，它会更容易。我曾经需要根据视图中的每一行（代码是列名，[值] 是值）更新表。因为视图是动态的，每次返回不同的行，因此不同的列值对。我在不使用任何存储过程的情况下重新编写了查询。
DECLARE @TSQL NVARCHAR(MAX), @Id VARCHAR(10) = 50
SELECT @TSQL = COALESCE(@TSQL + '''
,','') + Code + ' = ''' + [Value]
FROM view
WHERE Id = @Id
SET @TSQL = N'UPDATE tableName
SET ' + @TSQL + '''
WHERE Id = ' + @Id
PRINT @TSQL
--EXEC SP_EXECUTESQL @TSQL
PRINT @TSQL:
PRINT @TSQL：
UPDATE tableName
SET Discussed = 'Yes'
,Other = 'Blue'
,IntakeRating = 'H: < k'
,IntakeValueEstimate = '25'
,OpportunityDecision = 'Intake'
,Fee = '33 1/3'
,Fee2 = '40'
,IsManager = 'Yes'
WHERE Id = 50
回答by Walter Stabosz
This loop works, but it was slow for my SP. I think the correct answer is by HLGEM, your best bet is to write a better bulk query.
这个循环有效，但对我的 SP 来说很慢。我认为正确的答案是 HLGEM，最好的办法是编写更好的批量查询。
DECLARE @id INT
SET @id = 0
DECLARE @max INT
SELECT TOP 1 @max = TableID
FROM dbo.Table
ORDER BY TableID desc
-- loop until BREAK
-- this is how you can perform a DO-WHILE loop in TSQL
WHILE (1 = 1)
BEGIN
SELECT
TOP 1 @id = TableID
FROM dbo.Table
WHERE TableID > @id
IF @id IS NOT NULL
BEGIN
-- call you SP here
PRINT @id
END
-- break out of the loop once the max id has been reached
IF @id = @max BREAK
END
