使用游标或 while 循环或任何其他方式？

发布于 2024-11-05 09:38:33 字数 858 浏览 4 评论 0原文

我已经经历了几次与此相关的讨论（不准确），但我想知道针对我的情况的正确解决方案，因此发布了这个问题。

我需要根据某些条件从表中选择一些记录并将每个记录值传递给 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.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

洛阳烟雨空心柳 2024-11-12 09:38:33

看起来像是一种迂回的方法：

UPDATE Test SET isValid = 0 WHERE isValid = 1

至于哪种方法最好，您没有提供足够的细节来回答。

这完全取决于您想要做什么。也许递归 CTE 可以解决您的问题，而无需调用存储过程。也许一个好的加入可以解决这个问题。也许不同的解决方案是最好的。

您需要解释您想要实现的目标到底。

Looks like a round about way to do:

UPDATE Test SET isValid = 0 WHERE isValid = 1

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.

回复收藏 0 原文