Cursor in SQL Server

 

Introduction 

 

A cursor in SQL is a temporary work area created in system memory when a SQL statement is executed. A SQL cursor is a set of rows together with a pointer that identifies a current row. It is a database object to retrieve data from a result set one row at a time. It is useful when we want to manipulate the record of a table in a singleton method, in other words, one row at a time. In other words, 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 active set.

 

Types of Cursors in SQL

 

There are the following two types of cursors in SQL:

1. Implicit Cursor
2. Explicit Cursor

Implicit Cursor

 

These types of cursors are generated and used by the system during the manipulation of a DML query (INSERT, UPDATE, and DELETE). An implicit cursor is also generated by the system when a single row is selected by a SELECT command.

 

Explicit Cursor

 

This type of cursor is generated by the user using a SELECT command. An explicit cursor contains more than one row, but only one row can be processed at a time. An explicit cursor moves one by one over the records. An explicit cursor uses a pointer that holds the record of a row. After fetching a row, the cursor pointer moves to the next row.

 

Main components of Cursors

 

Each cursor contains the followings 5 parts,

1. Declare Cursor: In this part, we declare variables and return a set of values.
2. Open: This is the entering part of the cursor.
3. Fetch: Used to retrieve the data row by row from a cursor.
4. Close: This is an exit part of the cursor and used to close a cursor.
5. Deallocate: In this part, we delete the cursor definition and release all the system resources associated with the cursor.

1. SET NOCOUNT ON  
2. DECLARE @EMP_ID INT  
3. DECLARE @EMP_NAME NVARCHAR(MAX)  
4. DECLARE @EMP_SALARY INT  
5. DECLARE @EMP_CITY NVARCHAR(MAX)  
6.   
7. DECLARE EMP_CURSOR CURSOR  
8. LOCAL  FORWARD_ONLY  FOR  
9. SELECT * FROM Employee  
10. OPEN EMP_CURSOR  
11. FETCH NEXT FROM EMP_CURSOR INTO  @EMP_ID ,@EMP_NAME,@EMP_SALARY,@EMP_CITY  
12. WHILE @@FETCH_STATUS = 0  
13. BEGIN  
14. PRINT  'EMP_ID: ' + CONVERT(NVARCHAR(MAX),@EMP_ID)+  '  EMP_NAME '+@EMP_NAME +'  EMP_SALARY '  +CONVERT(NVARCHAR(MAX),@EMP_SALARY)  +  '  EMP_CITY ' +@EMP_CITY  
15. FETCH NEXT FROM EMP_CURSOR INTO  @EMP_ID ,@EMP_NAME,@EMP_SALARY,@EMP_CITY  
16. END  
17. CLOSE EMP_CURSOR  
18. DEALLOCATE EMP_CURSOR