Management Studio doesn't show results from cursor

Question

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.

Answer 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.