Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question

1 Answer 1

up vote 1 down vote accepted

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
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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