how cursor implementations are different for each cursor type in sql server

Question

In Oracle, there are only 2 types of cursor i.e. Implicit and Explicit cursor. Which is easy to understand. But, in SQL Server there are 4 Cursor AFAIK i.e. Static,Dynamic,Forward Only and Scroll. Which I feel very difficult to understand.

I got code for forward only,dynamic and static cursor and except cursor declaration everything is same. So, my question is, how they are different ?

declare @id int
declare @ename varchar(20)
declare @cnt int
declare cur_emp cursor forward_only for select sid,ename from emp
open cur_emp
set @cnt=@@cursor_rows
while @cnt>0
begin
set @cnt=@cnt-1
print @cnt
fetch next from cur_emp into @id,@ename
print cast(@id as varchar)+' '+@ename
end
close cur_emp
deallocate cur_emp

Even, above code doesn't showing any output except Command(s) completed successfully.. But, when I change the cursor declaration to static. Then, it is showing some output. Please help me.

Answer 1

This is straight out of SQL BOL:

Static cursors detect few or no changes, but consume relatively few resources while scrolling. Dynamic cursors detect all changes, but consume more resources while scrolling. Keyset-driven cursors lie in between, detecting most changes, but with less resource demands than dynamic cursors.

Although the database API cursor models consider a forward-only cursor to be a distinct type of cursor, SQL Server does not. SQL Server considers both forward-only and scroll as options that can be applied to static, keyset-driven, and dynamic cursors.

In the example provided, @@cursor_rows will return -1, because your cursor is dynamic, and your WHILE will therefore not loop. @@cursor_rows ONLY works with static cursors.

For your cursor above to start working, you either need a STATIC keyword in your cursor declaration, or fetch next from cur_emp into @id,@ename before the while, and change the while to WHILE @@FETCH_STATUS = 0

Rewritten:

declare @id int
declare @ename varchar(20)
declare @cnt int
declare cur_emp cursor forward_only for select sid,ename from emp
open cur_emp
fetch next from cur_emp into @id,@ename
while @@FETCH_STATUS = 0
 begin
    set @cnt=@cnt-1
    print @cnt
    fetch next from cur_emp into @id,@ename
    print cast(@id as varchar)+' '+@ename
 end
close cur_emp
deallocate cur_emp