SQL Server Cursor

A SQL cursor is a database object that is used to retrieve data from a result set one row at a time. A SQL cursor is used when the data needs to be updated row by row.

In a SQL server database, a select statement returns a set of rows which is called a result set, But sometimes we want to work with a row at a time rather than the entire result set at once, In this situation you can use SQL Server cursor.

Why Use a Cursor in SQL Server

• It allows positioning at specific rows of the result set.
• It supports data modifications to the rows at the current position in the result set.
• Modifying data with a predefined set of data as the input to the cursor.
• Cursors can provide the first few rows before the whole result set is assembled.

Following are the diagram of CURSOR Life cycle.

Lets understand the cursor life cycle step by step.

DECLARING CURSOR

First we declare a cursor as given below.

DECLARE <Cursor_name> CURSOR FOR <SQL_Query>

Cursor_name – Provide cursor name

SQL_Query – It will be a SELECT statement

DECLARE Cursor_name CURSOR FOR SELECT COL1, COL2,  
FROM tbl WHERE CONDITION..

OPENING CURSOR

Once you declare a cursor then, you need to retrieve the data from the SQL_query result which is done using the OPEN command as given below.

OPEN <Cursor_name>

FETCHING ROWS

Just right after opening the cursor, the SQL_query is executed and the result set is stored in memory. The next step is to fetch row’s data and store the values in declared variables. For each column that we have in our SQL_Query there must be a declared variable to hold the value of those columns.

Variable datatype and column datatype must match also make sure that the order of the variables must be the same as the result set columns order.

FETCH NEXT FROM <cursor_name> [ INTO @Col1, @Col2 [ ,...n ] ]

CLOSING CURSOR

Once operation is done, It is must to close the cursor. It releases the current result set and removes the locks from the rows used by the cursor.

CLOSE <Cursor_name>

DEALLOCATIG CURSOR

After closing the cursor, you should also deallocating the cursor, that releases all the system resources associated with the cursor.

DEALLOCATE <Cursor_name>

During the operations cursor uses function @@FETCH_STATUS

@@FETCH_STATUS is a system function that returns the status of the last FETCH statement issued against any opened cursor. It returns an integer value.

DECLARE @COL1 VARCHAR(50)

DECLARE @COL1 VARCHAR(50)
 
DECLARE Cursor_name CURSOR FOR SELECT COL1, COL2, FROM tbl 
WHERE CONDITION..
 
OPEN Cursor_name
 
FETCH NEXT FROM Cursor_name INTO @COL1, @COL2
 
WHILE @@FETCH_STATUS = 0

  BEGIN

    FETCH NEXT FROM Cursor_name INTO @COL1, @COL2

  END
  
CLOSE Cursor_name

DEALLOCATE Cursor_name

The purpose of this function is using it within while loop to keep fetching rows while the fetch statement is successful for that it returns value 0.

It returns -1 when there is no more row.

Following are the value that is returned by @@FETCH_STATUS

You can also read more about @@FETCH_STATUS function – click to read

Lets implement the SQL Server Cursor.

First we create a table EMPLOYEE_INFO and insert some records into it.

CREATE TABLE EMPLOYEE_INFO
(
EMP_ID INT, 
EMP_NAME VARCHAR(100), 
EMP_CODE VARCHAR(15)
)

INSERT INTO EMPLOYEE_INFO
(
EMP_ID, EMP_NAME, EMP_CODE, DEPT_ID)
VALUES(
(1, 'MANISH SINGH', 'EN-0034', 501),
(2, 'MANOJ RAWAT', 'EN-0035', 501),
(3, 'RAJESH KUMAWAT', 'EN-0036', 501),
(4, 'SURAJ K', 'EN-0037', 502),
(5, 'RUPESH SINGH', 'EN-0038', 502),
(6, 'ARJUN PAL', 'EN-0039', 503),
(7, 'GURPREET KAUR', 'EN-0040', 504),
(8, 'TRILOK KUMAR', 'EN-0041', 504),
(9, 'RANUK KHAN', 'EN-0041', NULL),
(10, 'MANDEEP GREWAL', 'EN-0041', NULL)

The following cursor is used for retrieving employee information from table EMPLOYEE_INFO .

Cursor keeps fetching the rows from table until the FETCH_STATUS value is 0.

When all rows are fetched then  @@FETCH_STATUS function returns -1.

DECLARE @emp_id INT, @emp_name VARCHAR(100), @emp_code VARCHAR(20);    

DECLARE emp_cr CURSOR FOR     

SELECT emp_id,emp_name,emp_code   

FROM Employee_info 

ORDER BY emp_id;    
  
OPEN emp_cr    

FETCH NEXT FROM emp_cr     

INTO @emp_id, @emp_name, @emp_code    

WHILE @@FETCH_STATUS = 0    

BEGIN    

PRINT '' + CAST(@emp_id as varchar(10)) +' '+ @emp_name+ '' +  @emp_code  

  FETCH NEXT FROM emp_cr    

INTO @emp_id,@emp_name,@emp_code    
   
END     

CLOSE emp_cr;    

DEALLOCATE emp_cr;

Here is the output of CURSOR.

In above scenario, the cursor sets its position to the first row returned by the first SELECT and fetches the employee information such as EMP_ID, EMP_NAME, EMP_CODE values from EMPLOYEE_INFO table .

Then the second SELECT uses the variable value to get data from EMPLOYEE_INFO and the next row is fetched.

Cursor keeps fetching the rows from table until @@FFETCH_STATUS function returns -1.

Also Read..

SQL Triggers