Simple Cursor Example in SQL

·         Cursors

 

DECLARE @EmployeeCode varchar(10)

DECLARE @EmployeeDesc varchar(10)

DECLARE c3 CURSOR READ_ONLY   --define or create new cursor.

FOR select EmployeeCode,EmployeeDesc from Employee

open c3       --opens and populates cursor by executing  select statement..

FETCH NEXT FROM c3      --retrives a row from cursor.

INTO @EmployeeCode,@EmployeeDesc

WHILE @@FETCH_STATUS=0        --will be 1 till it gets the record else returns 0

BEGIN

               

                print @EmployeeCode + ' ' + @EmployeeDesc

                        FETCH NEXT FROM c3

                INTO @EmployeeCode,@EmployeeDesc            

               

END

CLOSE c3

DEALLOCATE c3   --Destroys the cursor and releses Resources

Statements for declaring the cursor using join

Declare @EmployeeCode varchar(10),@EmployeeDesc varchar(10);

DECLARE cursor1 cursor

FOR

SELECT Employee.EmployeeCode,Employee.EmployeeDesc

FROM Employee JOIN Department ON employee.DeptCode=Department.DeptCode

open cursor1

FETCH NEXT FROM cursor1

INTO @EmployeeCode,@EmployeeDesc

WHILE @@FETCH_STATUS=0

BEGIN

            print Cast(@EmployeeCode AS varChar(max))+ ' '+ @EmployeeDesc

             

        FETCH NEXT FROM cursor1

        INTO @EmployeeCode,@EmployeeDesc

END

CLOSE cursor1;

DEALLOCATE cursor1;

·         USE OF UPDATE STATEMENT WITHIN CURSOR

DECLARE c3 CURSOR             --define or create new cursor.

FOR SELECT EmployeeCode,EmployeeDesc

FROM  Employee 

open c3                 --opens and populates cursor by executing select statement..

FETCH NEXT FROM c3      --retrives a row from cursor.

WHILE @@FETCH_STATUS=0

BEGIN      

       

        update  Employee set  Employee.EmployeeDesc = 'Manager' where EmployeeCode='aaa'       

       

                FETCH NEXT FROM c3

               

END

CLOSE c3

DEALLOCATE c3