SQL Cursor Example

SQL Cursor Life Cycle

The following steps are involced in a SQL cursor life cycle. 

1. Declaring Cursor
   A cursor is declared by defining the SQL statement.
   
2. Opening Cursor
   A cursor is opened for storing data retrieved from the result set.
   
3. Fetching Cursor
   When a cursor is opened, rows can be fetched from the cursor one by one or in a block to do data manipulation.
   
4. Closing Cursor
   The cursor should be closed explicitly after data manipulation.
   
5. Deallocating Cursor
   Cursors should be deallocated to delete cursor definition and release all the system resources associated with the cursor.

Why use a SQL Cursor?

In relational databases, operations are made on a set of rows. For example, a SELECT statement returns a set of rows which is called a result set. Sometimes the application logic needs to work with one row at a time rather than the entire result set at once. This can be done using cursors.

In programming, we use a loop like FOR or WHILE to iterate through one item at a time, the cursor follows the same approach and might be preferred because it follows the same logic.

SQL Cursor Syntax

1. DECLARE cursor_name CURSOR [ LOCAL | GLOBAL ]   
2. [ FORWARD_ONLY | SCROLL ]  
3.  [ STATIC | KEYSET | DYNAMIC | FAST_FORWARD ]   
4. [ READ_ONLY | SCROLL_LOCKS | OPTIMISTIC ]   
5. [ TYPE_WARNING ] FOR select_statement  
6.  [ FOR UPDATE [ OF column_name [ ,...n ] ] ] [;]   

Cursor Example

The following cursor is defined for retrieving employee_id and  employee_name from Employee table.The FETCH_STATUS value is 0 until there are rows.when all rows are fetched then  FETCH_STATUS becomes 1.

1. use Product_Database  
2. SET NOCOUNT ON;    
3.   
4. DECLARE @emp_id int ,@emp_name varchar(20),    
5.     @message varchar(max);    
6.   
7. PRINT '-------- EMPLOYEE DETAILS --------';    
8.   
9. DECLARE emp_cursor CURSOR FOR     
10. SELECT emp_id,emp_name    
11. FROM Employee  
12. order by emp_id;    
13.   
14. OPEN emp_cursor    
15.   
16. FETCH NEXT FROM emp_cursor     
17. INTO @emp_id,@emp_name    
18.   
19. print 'Employee_ID  Employee_Name'       
20.   
21. WHILE @@FETCH_STATUS = 0    
22. BEGIN    
23.     print '   ' + CAST(@emp_id as varchar(10)) +'           '+  
24.         cast(@emp_name as varchar(20))  
25.   
26.       
27.     FETCH NEXT FROM emp_cursor     
28. INTO @emp_id,@emp_name    
29.    
30. END     
31. CLOSE emp_cursor;    
32. DEALLOCATE emp_cursor;    

The Output of the above program will be as follows

What are the limitations of a SQL Cursor

A cursor is a memory resident set of pointers -- meaning it occupies memory from your system that may be available for other processes.

Cursors can be faster than a while loop but they do have more overhead.

Another factor affecting cursor speed is the number of rows and columns brought into the cursor. Time how long it takes to open your cursor and fetch statements.

Too many columns being dragged around in memory, which are never referenced in the subsequent cursor operations, can slow things down.

The cursors are slower because they update tables row by row. 

How can we replace SQL Cursors 

There's one replacement for cursors in SQL server joins.

Suppose we have to retrieve data from two tables simultaneously by comparing primary keys and foreign keys. In these types of problems, the cursor gives very poor performance as it processes through each and every column. On the other hand using joins in those conditions is feasible because it processes only those columns which meet the condition. So here joins are faster than cursors.

The following example explains the replacement of cursors through joins.

Suppose, we have two tables, ProductTable and Brand Table. The primary key of BrandTable is brand_id which is stored in ProductTable as foreign key brand_id. Now suppose, I have to retrieve brand_name from BrandTable using foreign key brand_id from ProductTable. In these situations cursor programs will be as follows,

1. use Product_Database  
2. SET NOCOUNT ON;    
3.   
4. DECLARE @brand_id int     
5. DECLARE @brand_name varchar(20)   
6.   
7.   
8. PRINT '--------Brand Details --------';    
9.   
10. DECLARE brand_cursor CURSOR FOR     
11. SELECT distinct(brand_id)  
12. FROM ProductTable;   
13.   
14. OPEN brand_cursor    
15.   
16. FETCH NEXT FROM brand_cursor     
17. INTO @brand_id    
18.   
19. WHILE @@FETCH_STATUS = 0    
20. BEGIN    
21.     select brand_id,brand_name from BrandTable where brand_id=@brand_id  
22. --(@brand_id is of ProductTable)  
23.       
24.     FETCH NEXT FROM brand_cursor     
25. INTO @brand_id   
26.    
27. END     
28. CLOSE brand_cursor;    
29. DEALLOCATE brand_cursor;    

The Output of the above program will be as follows

The same program can be done using joins as follows,

Select distinct b.brand_id,b.brand_name from BrandTable b inner join

ProductTable p on b.brand_id=p.brand_id

The Output of the above program will be as follows

As we can see from the above example, using joins reduces the lines of code and gives faster performance in case huge records need to be processed.