Sql-server – Cursor doesn’t print text when I tell it to

cursorssql serversql-server-2008

I have used WAITFOR combined with a cursor in the following script:

create table orders(orderId int primary key,productId int,productName varchar(20));

insert into orders values(1,11,'book');
insert into orders values(2,13,'flower');
insert into orders values(3,24,'microwave');
insert into orders values(4,7,'food');
insert into orders values(5,46,'clothes');

declare cur cursor for   
select * from orders;
open cur;
fetch next from cur;
while(@@fetch_status=0)
begin
    waitfor delay '00:00:01';
    fetch next from cur;
end;
close cur;
deallocate cur;

I'm looking to display one record from the cursor per the specified interval of 1 second. For example: display the first row, then wait a second; display the second row, then wait a second; and so on. The cursor above does not do that. Say orders has 5 rows. The WHILE block waits 5 seconds and then displays all records together.

Is this the cursor's fault? How can I modify this script to work as intended?

Best Answer

Add RAISERROR('', 0, 1) WITH NOWAIT before the delay to flush anything in the buffer to the client (SSMS?) first otherwise SQL Server will wait for more data to fill the packet.

Declare cur cursor for   
select * from orders   
open cur
fetch next from cur
while(@@fetch_status=0)
begin
    RAISERROR('', 0, 1) WITH NOWAIT
    WAITFOR DELAY '00:00:01'
    fetch next from cur
end
close cur
deallocate cur
Related Question