-1

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.

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.