SQL - 为每条记录调用存储过程
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2077948/
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 record
提问by Chris
I am looking for a way to call a stored procedure for each record of a select statement.
我正在寻找一种为 select 语句的每条记录调用存储过程的方法。
SELECT @SomeIds = (
SELECT spro.Id
FROM SomeTable as spro
INNER JOIN [Address] addr ON addr.Id = spro.Id
INNER JOIN City cty ON cty.CityId = addr.CityId
WHERE cty.CityId = @CityId
)
WHILE @SomeIds IS NOT NULL
BEGIN
EXEC UpdateComputedFullText @SomeIds
END
Such a thing above is not working of course, but is there a way to do something like that?
上面这样的事情当然行不通，但是有没有办法做这样的事情？
回答by treaschf
You need to use a cursor for that.
您需要为此使用游标。
DECLARE @oneid int -- or the appropriate type
DECLARE the_cursor CURSOR FAST_FORWARD
FOR SELECT spro.Id
FROM SomeTable as spro
INNER JOIN [Address] addr ON addr.Id = spro.Id
INNER JOIN City cty ON cty.CityId = addr.CityId
WHERE cty.CityId = @CityId
OPEN the_cursor
FETCH NEXT FROM the_cursor INTO @oneid
WHILE @@FETCH_STATUS = 0
BEGIN
EXEC UpdateComputedFullText @oneid
FETCH NEXT FROM the_cursor INTO @oneid
END
CLOSE the_cursor
DEALLOCATE the_cursor
回答by Charles Bretana
Put the Ids into a Temporary table variable, and then iterate throught each row: (You do not need to use a cursor which will be considerably slower)
将 Id 放入临时表变量中，然后遍历每一行：（您不需要使用会显着变慢的游标）
Declare @Keys Table (key integer Primary Key Not Null)
Insert @Keys(key)
SELECT spro.Id
FROM SomeTable as spro
JOIN [Address] addr ON addr.Id = spro.Id
JOIN City cty ON cty.CityId = addr.CityId
WHERE cty.CityId = @CityId
-- -------------------------------------------
Declare @Key Integer
While Exists (Select * From @Keys)
Begin
Select @Key = Max(Key) From @Keys
EXEC UpdateComputedFullText @Key
Delete @Keys Where Key = @Key
End
EDIT Delete is not slow when used with a filter predicate driven against a very narrow unique index, as this is. But it can easily be avoided, just by making loop as follows:
EDIT Delete 在与针对非常窄的唯一索引驱动的过滤器谓词一起使用时并不慢，因为这就是。但它可以很容易地避免，只需通过如下循环：
Declare @Key Integer = 0
While Exists (Select * From @Keys
Where key > @Key)
Begin
Select @Key = Min(Key) From @Keys
Where key > @Key
EXEC UpdateComputedFullText @Key
-- Delete @Keys Where Key = @Key No Longer necessary
End
回答by Hassan Syed
Surprised no one gave you an up-to-date answer. Cursors are bad. What you want is to move the logic of the SP into a table-valued-function(TVF) and then use CROSS APPLY
没想到没有人给你一个最新的答案。光标不好。您想要的是将 SP 的逻辑移动到表值函数（TVF）中，然后使用CROSS APPLY
Here is a query I wrote yesterday (don't dwell on the details, just look at the CROSS APPLY). The CROSS APPLYcreates a union of tables. Each element of this union is generated from the TVF which is parameterised on the row entries of the select statement.
这是我昨天写的一个查询（不要纠缠于细节，只看CROSS APPLY）。在CROSS APPLY创建表的联合。这个联合的每个元素都是从 TVF 生成的，TVF 在 select 语句的行条目上参数化。
SELECT supt.hostname,supt.scriptname, COUNT(*)
FROM Event_Pagehit eph
INNER JOIN Symboltable_urlpair supf
ON eph.fromPagePair=supf.id
INNER JOIN Symboltable_urlpair supt
ON supt.id=eph.toPagePair
CROSS APPLY dbo.TDFCompanyFormationsUrlClassification(supf.hostname,supf.scriptname) as x
CROSS APPLY dbo.TDFCompanyFormationsUrlClassification(supt.hostname,supt.scriptname) as y
WHERE x.isCompanyFormations=1
AND y.isCompanyFormations=0
GROUP BY supt.hostname,supt.scriptname
ORDER BY COUNT(*) desc
I can use xand yas if they were tables pulled in from the FROMor JOINclauses. If I had to write this query without a TVF it would span a couple of hundred lines.
我可以使用xandy就好像它们是从FROMorJOIN子句中提取的表一样。如果我必须在没有 TVF 的情况下编写此查询，它将跨越几百行。
Note:
笔记：
If you can't rewrite the SP: you should be able to insert the result of a stored procedure into the result table from a table valued function. I have never done this, and sometimes the different SQL server construct have caveats -- So unless someone says otherwise I assume this is the case.
如果您不能重写 SP：您应该能够将存储过程的结果从表值函数插入到结果表中。我从来没有这样做过，有时不同的 SQL 服务器结构有警告——所以除非有人说否则我认为是这种情况。
回答by Jose Chama
Try this one without cursor
试试这个没有光标的
DECLARE @id int
SELECT top 1 @id = spro.Id
FROM SomeTable as spro
INNER JOIN [Address] addr ON addr.Id = spro.Id
INNER JOIN City cty ON cty.CityId = addr.CityId
WHERE cty.CityId = @CityId
ORDER BY spro.id
WHILE @@ROWCOUNT > 0
BEGIN
EXEC UpdateComputedFullText @id
SELECT top 1 @id = spro.Id
FROM SomeTable as spro
INNER JOIN [Address] addr ON addr.Id = spro.Id
INNER JOIN City cty ON cty.CityId = addr.CityId
WHERE cty.CityId = @CityId
and spro.id > @id
ORDER BY spro.id
END
回答by Bob Palmer
Both of the answers above RE cursors are correct. However, based on the complexity of the code running inside of the cursor, you may be better served in dropping this into your language of choice and performing your calculations in code before dropping the results to a database.
RE 游标上方的两个答案都是正确的。但是，基于游标内部运行的代码的复杂性，您最好将其放入您选择的语言中，并在将结果放入数据库之前在代码中执行计算。
I've found myself going back and reviewing a lot of cursor operations, and in many cases, transitioning these to code for performance reasons.
我发现自己回过头来回顾了很多游标操作，并且在许多情况下，出于性能原因将这些转换为代码。
回答by Rubens Farias
You'll need to use a cursor: SQL Server Cursor Examples
您需要使用游标：SQL Server 游标示例
DECLARE @id int
DECLARE cursor_sample CURSOR FOR
SELECT spro.Id
FROM SomeTable as spro
INNER JOIN [Address] addr ON addr.Id = spro.Id
INNER JOIN City cty ON cty.CityId = addr.CityId
WHERE cty.CityId = @CityId
OPEN cursor_sample
FETCH NEXT FROM cursor_sample INTO @id
WHILE @@FETCH_STATUS = 0
BEGIN
EXEC UpdateComputedFullText @id
FETCH NEXT FROM cursor_sample INTO @id
END
CLOSE cursor_sample
DEALLOCATE cursor_sample
回答by bretlowery
Do you really need to do row-by-row processing when set processing is available?
有集合处理的时候，真的需要逐行处理吗？
You could put the results of the SELECT into a temp table, then call a proc to perform bulk SQL against the contents of the temp table. The temp table will be available to the called proc based on T-SQL scoping rules.
您可以将 SELECT 的结果放入临时表中，然后调用 proc 对临时表的内容执行批量 SQL。临时表将可用于基于 T-SQL 范围规则的调用过程。
回答by user3326308
The standard cursor solution is evil upon evil. Two identical FETCH NEXT statements are just a maintenance nightmare.
标准的游标解决方案是邪恶的。两个相同的 FETCH NEXT 语句只是维护的噩梦。
better is
更好的是
...declare cursor etc.
While 1=1
Fetch ...
if @@FETCH_STATUS <> 0 BREAK
...
End -- While
..Close cursor etc.
An evil sometimes justified. Just try to devise a set based approach to sending notification emails using sp_send_dbmail or other stored procedure.
邪恶有时是有道理的。只需尝试设计一种基于集合的方法来使用 sp_send_dbmail 或其他存储过程发送通知电子邮件。
