FOR UPDATE 游标正在返回每行的结果集！

发布于 2024-10-08 22:56:48 字数 1143 浏览 1 评论 0原文

我在 SQL 2005 上使用 UPDATE 游标，如下所示：（

DECLARE myCursor CURSOR FOR
        SELECT RowID, Value FROM myTable
        FOR UPDATE OF Value;
OPEN myCursor;
FETCH NEXT FROM myCursor
WHILE (@@FETCH_STATUS <> -1)
    UPDATE myTable SET Value = 42
    WHERE CURRENT OF myCursor
    FETCH NEXT FROM myCursor
END
CLOSE myCursor
DEALLOCATE myCursor

感谢 Matt 在我之前的问题关于这个游标语法。是的，我确实需要一个游标，因为每一行的新值实际上基于依赖于先前行的复杂计算。）

这可以正常工作，更新所有价值观。问题是它为每行更新返回一个结果集，由 RowID、Value 组成（有趣的是，它显示了更新行之前的结果）。最终，我收到以下错误：

查询次数已超出最大限制可以得到的结果集数量显示在结果网格中。仅有的前 100 个结果集是显示在网格中。

有什么办法可以抑制这些结果集吗？ SET NOCOUNT ON 并不能解决问题。这只是我在 SSMS 中直接运行它时看到的问题吗？或者当我将此游标放入存储过程中时，它实际上会尝试返回数百个结果集吗？

编辑：看起来它与更新无关。

使用FETCH NEXT FROM myCURSOR我实际上的方式确实返回了游标下一行的结果集。

如果我将其更改为 FETCH NEXT FROM myCURSOR INTO @variables，则它不会返回结果集。

所以我想现在的问题是：由于我使用的是 WHERE CURRENT OF，所以我并不真正需要该变量。我想我可以将它们放入只是为了抑制结果集，但是有更好的方法吗？

原文

I'm using an UPDATE cursor as follows on SQL 2005:

DECLARE myCursor CURSOR FOR
        SELECT RowID, Value FROM myTable
        FOR UPDATE OF Value;
OPEN myCursor;
FETCH NEXT FROM myCursor
WHILE (@@FETCH_STATUS <> -1)
    UPDATE myTable SET Value = 42
    WHERE CURRENT OF myCursor
    FETCH NEXT FROM myCursor
END
CLOSE myCursor
DEALLOCATE myCursor

(Thanks to Matt for his correct answer on my prior question concerning this cursor syntax. And yes, I do need a cursor, because each row's new value is actually based on a complicated calculation that depends on the prior rows.)

This works correctly, updating all the Values. The problem is that it returns a result set for each row updated, consisting of RowID, Value (interestingly, its showing the result from before the row is updated). Eventually, I get the following error:

The query has exceeded the maximum
number of result sets that can be
displayed in the results grid. Only
the first 100 result sets are
displayed in the grid.

Any way to suppress these result sets? SET NOCOUNT ON doesn't do the trick.
Is this just an issue I see running it directly in SSMS? Or will it actually try to return hundreds of result sets when I put this cursor inside a stored proc?

EDIT: Looks like it has nothing to do with the UPDATE.

Using FETCH NEXT FROM myCURSOR the way I am actually does return a result set of the next row from the cursor.

If I change it to FETCH NEXT FROM myCURSOR INTO @variables, then it doesn't return a result set.

So I guess the question now is: Since I'm using WHERE CURRENT OF, I don't really need the variable. I guess I can put them in just to suppress the result set, but is there a better way to do it?

分享到QQ

分享到微博

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

发布评论

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

丶情人眼里出诗心の 2024-10-15 22:56:48

注意同时开始...结束
并获取到

Declare @row int
Declare @value int
DECLARE myCursor CURSOR FOR
        SELECT RowID, Value FROM myTable
        FOR UPDATE OF Value;
OPEN myCursor;
FETCH NEXT FROM myCursor into @row, @value
WHILE (@@FETCH_STATUS <> 1)
begin   
    UPDATE myTable SET Value = 42
    WHERE CURRENT OF myCursor
    FETCH NEXT FROM myCursor into @row, @value
END
CLOSE myCursor
DEALLOCATE myCursor

Note while begin ... end
and Fetch into

Declare @row int
Declare @value int
DECLARE myCursor CURSOR FOR
        SELECT RowID, Value FROM myTable
        FOR UPDATE OF Value;
OPEN myCursor;
FETCH NEXT FROM myCursor into @row, @value
WHILE (@@FETCH_STATUS <> 1)
begin   
    UPDATE myTable SET Value = 42
    WHERE CURRENT OF myCursor
    FETCH NEXT FROM myCursor into @row, @value
END
CLOSE myCursor
DEALLOCATE myCursor

回复收藏 0 原文

~没有更多了~