SQL Server CURSOR

For SQL Server Stored Procedures: Part-2, Click here.

Database cursor act like a pointer in a database. It process query on each row one by one. Cursor act like a pair of tweezers to pick up each row of data from bag of data. You should use cursor if you have large amount of data and need to act individually on each row. Cursor doesn’t need to allocate big chunk of space in server to load result set like in SQL.

Life cycle of SQL Server cursor:

Step for using a cursor:

 

First, Declare variables and cursor

DECLARE  cursorName  AS CURSOR

To declare cursor you should use DECLARE keyword followed by cursorName with its data type as CURSOR and SELECT statement for defining its result set.

Second, Open cursor

OPEN CURSOR cursorName

After SELECT statement open CURSOR using OPEN CURSOR keyword with cursorName.

Third, fetch a row form cursor into one or more variable

FETCH NEXT CURSOR FROM cursorName INTO variables

Fetch function use @@FETCHSTATUS to returns status of the last cursor. If @@FETCHSTATUS returns 0, it means fetching is successful. We can use WHILE statement to fetch each rows which can be done as:

WHILE @@FETCHSTATUS = 0
      BEGIN
         FETCH NEXT FROM cursorName
      END

Fourth, close the cursor

CLOSE cursorName

Lastly, deallocate the cursor

DEALLOCATE cursorName

Deallocation give permission to use same cursor name once again.

For Example:

DECLARE
     @firstName VARCHAR(MAX),
     @salary DECIMAL
DECLARE empCursor CURSOR
       FOR SELECT
           First_Name,
           Salary
       FROM
           dbo.EmployeeDb

OPEN empCursor

FETCH NEXT FROM empCursor INTO
           @firstName,
           @salary
      WHILE @@FETCH_STATUS = 0
            BEGIN
              PRINT @firstName +CAST(@salary AS VARCHAR)
              FETCH FROM empCursor INTO
                    @firstName,
                    @salary
            END

CLOSE empCursor

DEALLOCATE empCursor

Syntax description:

First, Declare two variable to hold First_Name and Salary, cursor name as empCursor  to hold the result of query that select name and salary from dbo.EmployeeDb.

Then, Open cursor as

OPEN empCursor

Next, fetch each rows from cursor and print the name and salary of employee.

FETCH NEXT FROM empCursor INTO
           @firstName,
           @salary 
           WHILE @@FETCH_STATUS = 0
                 BEGIN
                    PRINT @firstName +CAST(@salary AS VARCHAR)
                    FETCH FROM empCursor INTO
                          @firstName,
                          @salary
                 END

Next, close the cursor

CLOSE empCursor

At Last, deallocate cursor

DEALLOCATE empCursor

Output:

  For SQL Server Stored Procedure: Part-4, Click here.