Cursors in SQL Server

                                                                 CURSORS

Cursor is the location in memory where SQL Server tables are stored.

If memory location is created by SQLServer then  it is called as implicit cursor. Select statement will uses implicit cursors and it will show all records in the table at a time as a set

If memory location is created by programmer then it is called as  an explicit cursor, the tables stored in an explicit cursor is accessed row by row.

Cursor Life Cycle:

Declare:

In declare section of T-SQL program we are define cursor using following syntax

Declare cursor-name cursor for select statement

Ex: 

DECLARE cursor_emp CURSOR

FOR

SELECT *

FROM emp

Open:

Whenever we are opening the cursor automatically records are fetched from table and store it into cursor memory area

Fetch:

 We are fetching records from cursor and store it into T-SQL variables

Syn: Fetch from cursorname into variable name

Close:

                It closes the an opened cursor

                Close cursorname

Deallocate:

 Removes the cursor structure

                Deallocate cursorname

@@fetch_status:

                Retuns the state of the last cursor  fetch statement

                          Return Values                                       Description

                               

                               0                                            The fetch statement was successful.

                              -1                                            The fetch statement was failed or row                                                                                 was beyond the result set.

                               -2                                            The row fetched is missing.

Next:

                Returns the result row immediately following the current row and increments the row to the row returned.

First:

                Returns the first row in the cursor and makes it the current row.

Last:

                Returns the last row in the cursor and makes it current row.      

WAP Cursor program to print employee names and their salaries?

DECLARE empcur CURSOR FOR 

ELECT ename,sal 

     FROM   emp 

DECLARE @ename VARCHAR(20), 

        @sal   MONEY 

OPEN empcur 

FETCH next FROM empcur INTO @ename, @sal 

WHILE @@FETCH_STATUS = 0 

  BEGIN 

      PRINT 'salary of' + @ename + 'is' 

            + Cast(@sal AS VARCHAR) 

      FETCH next FROM empcur INTO @ename, @sal 

  END 

CLOSE empcur 

DEALLOCATE empcur 

 WA cursor to display emplyee monthly salry using global cursor

DECLARE empcur CURSOR global FOR 

 SELECT ename, 

         sal, 

         comm 

  FROM   emp 

DECLARE @ename    VARCHAR(50), 

        @sal      MONEY, 

        @comm     MONEY, 

        @totalsal MONEY 

OPEN empcur 

FETCH next FROM empcur INTO @ename, @sal, @comm 

WHILE @@FETCH_STATUS = 0 

  BEGIN 

      SET @totalsal =@sal + Isnull(@comm, 0) 

      PRINT @ename + 'Earns' + Cast(@totalsal AS VARCHAR) 

            + 'everymonth' 

      FETCH next FROM empcur INTO @ename, @sal, @comm 

  END 

CLOSE empcur 

Note: whenever we declare global cursor no need to deallocate it can be used for further program purpose

DECLARE @ename     VARCHAR(50), 

        @sal       MONEY, 

        @comm      MONEY, 

        @annualsal MONEY 

OPEN empcur 

FETCH next FROM empcur INTO @ename, @sal, @comm 

WHILE @@FETCH_STATUS = 0 

  BEGIN 

      SET @annualsal=( @sal + Isnull (@comm, 0) ) * 12 

      PRINT @ename + 'Earns' 

            + Cast(@annualsal AS VARCHAR) + 'Every Year' 

      FETCH next FROM empcur INTO @ename, @sal, @comm 

  END 

CLOSE empcur 

Ex:          Cursor to fetch alternate rows from emp table

DECLARE emp_cur scroll CURSOR FOR 

  SELECT empno, 

         ename 

  FROM   emp 

OPEN emp_cur 

DECLARE @empno INT, 

        @ename VARCHAR(40) 

FETCH next FROM emp_cur INTO @empno, @ename 

WHILE @@FETCH_STATUS !=- 1 

  BEGIN 

      PRINT Cast(@empno AS VARCHAR) + '   ' + @ename 

      FETCH relative 2 FROM emp_cur INTO @empno, @ename 

  END 

DEALLOCATE emp_cur 

Ex:          Write a cursor program which takes backup of all databases into d:\backups folder except system dbs

                

DECLARE backup_cur CURSOR FOR 

  SELECT NAME 

  FROM   sysdatabases 

  WHERE  NAME NOT IN( 'master', 'model', 'msdb', 'tempdb' ) 

DECLARE @filename VARCHAR(50), 

        @filepath VARCHAR(200) 

DECLARE @dbname VARCHAR(50) 

OPEN backup_cur 

FETCH next FROM backup_cur INTO @dbname 

WHILE @@fetch_status <>- 1 

  BEGIN 

      SET @filename=@dbname + '_' + CONVERT(VARCHAR, Getdate(), 112) 

                    + '.bak' 

      SET @filepath='d:\backups\' + @filename 

      BACKUP DATABASE @dbname TO DISK=@filepath 

      FETCH next FROM backup_cur INTO @dbname 

  END 

DEALLOCATE backup_cur