使用游标或 while 循环或任何其他方式？
我已经经历了几次与此相关的讨论（不准确），但我想知道针对我的情况的正确解决方案，因此发布了这个问题。
我需要根据某些条件从表中选择一些记录并将每个记录值传递给 SP。
我想到使用 Cursor 来循环记录集，经过一番搜索后我发现也使用了 while 循环，并且有很多关于哪种最好的讨论，大多数人说这取决于情况。现在我无法判断我的情况，因此请专家面前。
这是我想到的 while 循环：
Table Test
{
id int
value int
-- Some more fields
}
WHILE EXISTS(SELECT TOP 1 id FROM Test WHERE isValid=1)
BEGIN
DECLARE @id AS INT
DECLARE @value AS INT
SELECT TOP 1 @id=id, @value=value FROM Test WHERE isValid=1
EXEC SP_SomeProcessingSP @id, @value -- Some more fields passed to it from above table
-- After execution of the above SP I need to update the record to invalid
UPDATE Test SET isValid = 0 WHERE id=@id
END
这种方法比游标更好吗？如果任何人都可以在没有 while 和光标的情况下提出更好的解决方案，那就太好了（我想避免两者）。
编辑：修改了 while 块并给出了示例表。现在，在 while 块内调用的 SP 访问几个表中的数据，并进行一些处理并将数据插入到其他几个表中。
I've gone through several discussions related (not exact) to this but I want to know the correct solution to my situation hence posting this question.
I need to select some records from a table based on some condition and pass each record values to a SP.
I thought of using Cursor to loop through the record set, after some googling I found while loop is also used and many discussions on which one is best are there and most of them said that it depends on the situation. Now I'm not able to judge my situation hence putting in front of experts.
Here is the while loop I thought of:
Table Test
{
id int
value int
-- Some more fields
}
WHILE EXISTS(SELECT TOP 1 id FROM Test WHERE isValid=1)
BEGIN
DECLARE @id AS INT
DECLARE @value AS INT
SELECT TOP 1 @id=id, @value=value FROM Test WHERE isValid=1
EXEC SP_SomeProcessingSP @id, @value -- Some more fields passed to it from above table
-- After execution of the above SP I need to update the record to invalid
UPDATE Test SET isValid = 0 WHERE id=@id
END
Is this approach better than cursor? It would be great if anyone can come up with better solution without while and cursor (I want to avoid both).
Edit: Modified the while block and gave example table as well. Now the SP which is called inside this while block accesses data from couple of tables and do some processing and insert data into couple of other tables.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
看起来像是一种迂回的方法：
至于哪种方法最好，您没有提供足够的细节来回答。
这完全取决于您想要做什么。也许递归 CTE 可以解决您的问题，而无需调用存储过程。也许一个好的加入可以解决这个问题。也许不同的解决方案是最好的。
您需要解释您想要实现的目标到底。
Looks like a round about way to do:
As for what approach is best, you have not supplied enough detail for an answer.
It entirely depends on what you are trying to do. Perhaps a recursive CTE would solve your problems without the need to call a stored procedure. Perhaps a good join would solve it. Perhaps a different solution would be best.
You need to explain what exactly you are trying to achieve.
您可以修改存储过程以将表作为输入，然后选择表变量。对于更新，您可以重新连接该表变量。
在此处查看表值参数：http://msdn.microsoft.com/en-我们/library/bb510489.aspx
You could modify the stored procedure to take tables as inputs and then select into a table variable. For the update you can join back on that table variable.
Check out Table Valued Parameters here: http://msdn.microsoft.com/en-us/library/bb510489.aspx