T/SQL - Part Two

In this article we will learn about cursor and types of cursors in detail, with examples.

Cursor:cursor is a memory location for storing database tables. Cursor is a temporary work area allotted to the client at the server when a select stint is executed. A cursor contains information on a select stint and the rows of data accessed by it. This temporary work area is used to store the data retrieved from the database and manipulate this data.

A cursor can hold more than one row but can process only one row at a time The set of rows the cursor holds is called the result set.

Types of cursor
  • Implicit cursor
    These cursors will be created by SQL Server by default when select stint executes.

  • Explicit Cursor
    When user creates a memory location to store the tables then it is called  Explicit cursor
Whenever we want to do record by record manipulation the explicit cursor will be used.

Steps to create Explicit Cursor
  1. Declare a cursor
    In this process we define a cursor
    Syntax - Declare <cursor name> cursor for <Select stint>

  2. Open a cursor
    When we open a cursor it will internally execute the select stint that is associated with the cursor declaration and load the data into the cursor.
    Syntax - Open <Cursor name>

  3. Fetching Data from the cursor
    In this process we access row by row from cursor
    Syntax - Fetch first/last/next/prior /absolute n/relative n from <cursor name> [into <variables>]

  4. Closing a cursor
    In this process it releases the cursor result set of the cursor leaving the data structure available for reporting.
    Syntax - close <cursor name>

  5. Deallocate a cursor
    It removes the cursor reference and deallocates it by destroying the data structure.
    Syntax - Deallocate <cursor name>

    It is a global variable use to check whether cursor variable contains records or not. If the record is there the value will be zero, and otherwise the value will be -1
Fetching Methods in Cursor
  1. Next -It is a default method of cursor which can be used for fetching the records in forward directional only
  2. First - Fetching first record from the table
  3. Last - Fetching Last record from the table.
  4. Prior - Fetching the current record to previous record (backward directional)
  5. Absolute n - Fetching exact position of the record from the Table.n is the number of records.
  6. Relative n - Fetching the records either in incremental or decremental order.
The cursors are again classified into 2 types,
  1. Forward-only cursor
  2. Scroll cursor
If a cursor is declared as forward only, it allows you to navigate only to the next records in sequential order, and moreover it supports only a singleton fashion that is fetched next (in a one-by-one way)

Whereas a scroll cursor allows you to Navigate /Fetch bi-directionally; that is, top-bottom or bottom-top, and it supports six different fetch methods:
  1. Fetch Next
  2. Fetch First
  3. Fetch Last
  4. Fetch Prior
  5. Fetch absolute n
  6. Fetch relative n

Write a cursor to update Employee Salary as per given criteria. 

For each department name, the salary increases in the given percentage:
Department NameIncrement
Create table Empdetails and insert some values
  1. create table Empdetails(id int,dname varchar(50),salary int)  
  3. insert into Empdetails values(1,'Technology',2000)  
  4. insert into Empdetails values(2,'Sales',25000)  
  5. insert into Empdetails values(3,'Hr',12000)  
table Empdetails
Now we create a cursor.
  1. declare @id int,@dname varchar(50)  
  2. declare cur cursor for select id,dname from Empdetails  
  3. open cur  
  4. fetch next from cur into @id,@dname  
  5. while @@FETCH_STATUS=0  
  6. begin  
  7. if @dname='Development'  
  8. update Empdetails set salary=salary*5 where id=@id  
  9. else if @dname='sales'  
  10. update Empdetails set salary=salary*7 where id=@id  
  11. else  
  12. update Empdetails set salary =salary*3 where id=@id  
  13. fetch next from cur into @id,@dname  
  14. end   
  15. close cur  
  16. deallocate cur  
Check the table,


Write a cursor to fetch records from last to first:
  1. declare c1 cursor scroll for select * from company  
  2. open c1  
  3. fetch last from c1   
  4. while @@FETCH_STATUS=0  
  5. begin   
  6. fetch prior from c1  
  7. end  
  8. close c1  
  9. deallocate c1  



In this article we learned about cursor in T/SQL. Cursor is a memory location for storing database tables.