Management Studio 不显示光标的结果

发布于 2024-11-10 04:37:12 字数 710 浏览 5 评论 0原文

好吧，这可能很简单，但我找不到解决方案。我开始在 T-SQL 中使用游标并尝试它们。

但是，如果我通过执行按钮在 Management Studio 中执行结果，则不会返回结果。我得到的只是“命令执行成功”。

如果我调试它，我会得到结果，下次单击执行时，我也会得到结果......

是否有某种缓存？还是我做错了？

脚本看起来像这样：

    declare @po varchar(20), @prod varchar(50), @qty integer, @type varchar(20)

    declare db_cursor cursor for
    select product, po, qty, space(1) as btype from header
    for read only

    open db_cursor

    while @@FETCH_STATUS=0
    begin
      fetch db_cursor into @po, @prod, @qty, @type
      if @qty<1000
    set @type = 'small'
    else 
    set @type = 'large'
       print @type
    end

close db_cursor
deallocate db_cursor

PS：当然我在打印之前使用了选择，同样的问题。

原文

Ok, this might be an easy one, but I can't find a solution.
I'm beginning to work with cursors in T-SQL and am playing around with them.

However I don't get my results back if I execute them in Management Studio via the Execute-Button. All I get is "Command executed successfully".

If I debug it I get the results and the next time I click on execute I also get the results...

Is there some kind of cache? Or am I doing it wrong?

Script looks like this:

    declare @po varchar(20), @prod varchar(50), @qty integer, @type varchar(20)

    declare db_cursor cursor for
    select product, po, qty, space(1) as btype from header
    for read only

    open db_cursor

    while @@FETCH_STATUS=0
    begin
      fetch db_cursor into @po, @prod, @qty, @type
      if @qty<1000
    set @type = 'small'
    else 
    set @type = 'large'
       print @type
    end

close db_cursor
deallocate db_cursor

PS: naturally I used select before print, same issue.

分享到QQ

分享到微博

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

发布评论

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

通知家属抬走 2024-11-17 04:37:13

啊我明白你的意思了。第一次运行下面的脚本会产生结果。在随后的情况下，什么也不会发生（除非您在新的 SSMS 窗口中尝试）。

    declare @po varchar(20), @prod varchar(50), @qty integer, @type varchar(20)

    declare db_cursor cursor for
    select name, name, number, space(1) as btype from master..spt_values
    for read only

    open db_cursor

    while @@FETCH_STATUS=0
    begin
    print 'y'
      fetch db_cursor into @po, @prod, @qty, @type
      if @qty<1000
    set @type = 'small'
    else 
    set @type = 'large'
       print @type
    end

close db_cursor
deallocate db_cursor

问题在于您如何检查 @@FETCH_STATUS 值。这在新连接中从 0 开始，但您的脚本将其保留在 -1 处。

您需要在循环之前获取第一行。请参阅此博文了解正常模式。

Ah I see what you mean. On the first occasion the script below is run it produces results. On subsequent occasions nothing happens (unless you try in a new SSMS window).

    declare @po varchar(20), @prod varchar(50), @qty integer, @type varchar(20)

    declare db_cursor cursor for
    select name, name, number, space(1) as btype from master..spt_values
    for read only

    open db_cursor

    while @@FETCH_STATUS=0
    begin
    print 'y'
      fetch db_cursor into @po, @prod, @qty, @type
      if @qty<1000
    set @type = 'small'
    else 
    set @type = 'large'
       print @type
    end

close db_cursor
deallocate db_cursor

The problem is how you are checking the @@FETCH_STATUS value. This starts off at 0 in a new connection but your script leaves it at -1.

You need to Fetch the first row before the loop. See this blog post for the normal pattern.

回复收藏 0 原文