Cursors in SQL Server

Cursor in SQL Server:


Cursor is one of an important topic in SQL Server. This will enable us to loop through the result set.


Considering a scenario like, I need to loop through a table and based on the value from the table, I need to do some operation in the second table. In that case, we will go for cursors. Although, cursors are heavy weight objects and it is used in ages from sql server 7.0 version (may be before that too. Am not sure).


Let's go the implementation phase,


Am creating two tables one secondary table and the other is a reference table(Source table)


create table venkat (id int, id1 int)
create table venkatSecondTable (id int)
insert into venkat values(1,2)


The syntax is like,


1. Declare the cursor
2. Open the cursor.
3. Fetch the first record and store it into the cursor.
4. Do the operation until your cursor fetch status is empty.
5. Fetch the next record(A loop)
6. Close the cursor
7. Deallocate the cursor.


Let's see a small example query to check the working functionality of a cursor. Am creating a procedure venkatproc and trying into the insert the record into the secondary table by looping through the first table.

create procedure venkatproc
as
begin
declare @val int
declare cur cursor for select ID from venkat
open cur
fetch next from cur into @val
while @@FETCH_STATUS =0
begin
insert into venkatSecondTable(id) select id1 from venkat where id=@val
fetch next from cur into @val
end
close cur
deallocate cur
end


Most important points:


1. Cursor needs to be opened before using it.
2. We need to monitor memory leakages. If we didnt properly close or deallocate the cursor. It will cause serious memory issues(Holding the memory)
3. As a well known info, cursor is a heavy weight object. Instead we can opt while loop or cte's in sql server 2005.


Cheers,

Venkatesan Prabu. J