About Cursor in Sql Server - Database

All about Sql Server: example of Cursor in sql server

What is a Cursor in SQL Server?

A Cursor is a SQL Object, or we can say like a virtual table that retrieves data from the table one row at a time. We use cursors when we need to update records in a database table in singleton fashion in other words row by row.

Life Cycle of Cursor:

Declare Cursor - > Open -> Fetch - > Close -> Deallocate 

Before using a cursor, you first must declare the cursor. Once a cursor has been declared, you can open it and fetch from it. You can fetch row by row and make multiple operations on the currently active row in the cursor. When you have finished working with a cursor, you should close the cursor and Deallocate it to release SQL Server resources.

Type of cursors:

- Forward Only Cursor,

- Scroll Cursor,

- Static Cursor,

- Dynamic Cursor,

- Keyset Driven Cursor

First of all we create a table for applying our Cursor.

create table emp(emp_id int,em_name varchar(10))

After the creation we insert data as follows:

insert into emp(emp_id,em_name) VALUES(1,'d')

insert into emp(emp_id,em_name) VALUES(2,'deepak')

insert into emp(emp_id,em_name) VALUES(3,'gaurav')

insert into emp(emp_id,em_name) VALUES(4,'mahi')

insert into emp(emp_id,em_name) VALUES(5,'gill')

insert into emp(emp_id,em_name) VALUES(6,'singh')

Output:

Forward only and Scroll Cursors are important So I explain them in detail. 

Forward Only cursor: 

This type of Cursor fetches the next record only. In this type of cursor we can fetch only next record we cant fetch first, last and a specific record.

Declaration of Forward Only cursor:

declare curscr  cursor

for

select * from emp

Opening the forward-only Cursor

open curscr

Fetching data from a Forward Only cursor

fetch next from curscr

If we execute this query two times than we get the following output:

Closing the Forward Only Cursor:

close curscr

Deallocate/Deleting the Forward Only Cursor:

deallocate curscr

Scroll Cursor:

We can fetch any record as first, last, prior and specific record from the table.

Declaration of Scroll cursor:

declare scroll_cursor cursor

scroll for

select * from emp

Opening the Scroll Cursor:

open scroll_cursor

Fetching data from Scroll cursor:

- fetch first from scroll_cursor (To fetching the first data)

- fetch last from scroll_cursor (To fetching the last data)

- fetch prior from scroll_cursor (To fetching the previous data)

- fetch absolute 4 from scroll_cursor (To fetching the absolute data)

- fetch relative 2 from scroll_cursor (To fetching the relative data)

Closing the Scroll Cursor:

close scroll_cursor

Deallocate/Deleting the Scroll Cursor :

deallocate scroll_cursor

Syntax to Declare Cursor:

Declare Cursor SQL Command is used to define the cursor with many options that impact the scalability and loading behavior of the cursor. The basic syntax is given below:

 DECLARE cursor_name CURSOR

 [LOCAL | GLOBAL] --define cursor scope

 [FORWARD_ONLY | SCROLL] --define cursor movements (forward/backward)

 [STATIC | KEYSET | DYNAMIC | FAST_FORWARD] --basic type of cursor

 [READ_ONLY | SCROLL_LOCKS | OPTIMISTIC] --define locks

 FOR select_statement --define SQL Select statement

 FOR UPDATE [col1,col2,...coln] --define columns that need to be updated 

Example 1: 

SET NOCOUNT ON

DECLARE @Id int

DECLARE @name varchar(50)

DECLARE @salary int

DECLARE cur_emp CURSOR

STATIC FOR 

SELECT EmpID,EmpName,Salary from Employee

OPEN cur_emp

IF @@CURSOR_ROWS > 0

BEGIN 

FETCH NEXT FROM cur_emp INTO @Id,@name,@salary

WHILE @@Fetch_status = 0

BEGIN

PRINT 'ID : '+ convert(varchar(20),@Id)+', Name : '+@name+ ', Salary : '+convert(varchar(20),@salary)

FETCH NEXT FROM cur_emp INTO @Id,@name,@salary

END

END

CLOSE cur_emp

DEALLOCATE cur_emp

SET NOCOUNT OFF

Example 2:

CREATE PROCEDURE Usp_cursor_test

AS

  BEGIN

      –Declaring the  variables needed for cursor to store data

      DECLARE @Name VARCHAR(50)

      DECLARE @EmptypeID INT

      –Declaring the Cursor cur_print For name and Emptypeid in the Employeedetails table 

      DECLARE cur_print CURSOR FOR

        SELECT name,

               emptypeid

        FROM   employee.employeedetails

      –After declaring we have to open the cursor 

      OPEN cur_print

      –retreives the First row from cursor and storing it into the variables. 

      FETCH NEXT FROM cur_print INTO @Name, @EmptypeID

      – @@FETCH_STATUS returns the status of the last cursor FETCH statement issued against  

      – any cursor currently opened by the connection. 

      – @@FETCH_STATUS = 0 means The FETCH statement was successful. 

      – @FETCH_STATUS = -1 The FETCH statement failed or the row was beyond the result set. 

      – @@FETCH_STATUS = -2 The row fetched is missing. 

      WHILE @@FETCH_STATUS = 0

        BEGIN

            –Operations need to be done,Here just printing the variables 

            PRINT @Name

            PRINT @EmptypeID

            –retreives the NExt row from cursor and storing it into the variables. 

            FETCH NEXT FROM cur_print INTO @Name, @EmptypeID

        END

      –Closing the cursor 

      CLOSE cur_print

      – removes the cursor reference and relase cursor from memory 

      – very Important 

      DEALLOCATE cur_print

  END

Note:

• Once cursor is opened we have to close the cursor.
• After the usage cursor should be deallocated from the memory.
• I will not recommend the usage of cursors in all scenarios because it affects performance, since for each result it will have a  network round trip which will cause a major performance issue in large data sets. You can make use of case statement instead of cursors for some scenarios.