Mastering Cursors In SQL Server

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

use Product_Database  
SET NOCOUNT ON;    
  
DECLARE @emp_id int ,@emp_name varchar(20),    
    @message varchar(max);    
  
PRINT '-------- EMPLOYEE DETAILS --------';    
  
DECLARE emp_cursor CURSOR FOR     
SELECT emp_id,emp_name    
FROM Employee  
order by emp_id;    
  
OPEN emp_cursor    
  
FETCH NEXT FROM emp_cursor     
INTO @emp_id,@emp_name    
  
print 'Employee_ID  Employee_Name'       
  
WHILE @@FETCH_STATUS = 0    
BEGIN    
    print '   ' + CAST(@emp_id as varchar(10)) +'           '+  
        cast(@emp_name as varchar(20))  
  
      
    FETCH NEXT FROM emp_cursor     
INTO @emp_id,@emp_name    
   
END     
CLOSE emp_cursor;    
DEALLOCATE emp_cursor; 

DECLARE emp_cursor CURSOR FOR     
SELECT emp_id,emp_name    
FROM Employee  
order by emp_id;

Open a cursor

Once a cursor is declared, it must be opened before you can use it. The OPEN SQL statement is used to open a cursor. The following statement opens a cursor named emp_cursor.

OPEN emp_cursor 

Fetch data from a cursor

The FETCH NEXT FROM cursorname INTO statement is used to fetch data from a cursor into variables. Once the data is stored into variables, you can print or use these variables.

The following statement reads from emp_cursor and stores values into @emp_id and @emp_name variables. After that, the value of the variable is printed.

FETCH NEXT FROM emp_cursor     
INTO @emp_id, @emp_name  
print 'Employee_ID  Employee_Name' 

Loop through all rows in a cursor

We use the WHILE loop to read all rows in a cursor. The WHILE loop starts with a BEGIN and ends with an END. The FETCH_STATUS is used to check if any rows are left in the row set. If the FETCH_STATUS is 0, there are no more rows left to read.

The following code reads all rows using a cursor in SQL Server.

WHILE @@FETCH_STATUS = 0    
BEGIN    
    print '   ' + CAST(@emp_id as varchar(10)) +'           '+  
        cast(@emp_name as varchar(20))
    FETCH NEXT FROM emp_cursor
INTO @emp_id,@emp_name
END

Close a cursor

The CLOSE statement followed by the cursor name closes an opened cursor. The following SQL statement closes the cursor named emp_cursor.

CLOSE emp_cursor;

Deallocate a cursor

A cursor must be released from memory once it is done its work. The DEALLOCATE statement followed by the cursor name deallocates a cursor in SQL.

The following statement deallocates the emp_cursor cursor.

DEALLOCATE emp_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 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.

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 is 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 a 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.

Suppose, we have two tables, ProductTable, and Brand Table. The primary key of BrandTable is brand_id which is stored in ProductTable as a 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,

use Product_Database  
SET NOCOUNT ON;    
  
DECLARE @brand_id int     
DECLARE @brand_name varchar(20)   
  
  
PRINT '--------Brand Details --------';    
  
DECLARE brand_cursor CURSOR FOR     
SELECT distinct(brand_id)  
FROM ProductTable;   
  
OPEN brand_cursor    
  
FETCH NEXT FROM brand_cursor     
INTO @brand_id    
  
WHILE @@FETCH_STATUS = 0    
BEGIN    
    select brand_id,brand_name from BrandTable where brand_id=@brand_id  
--(@brand_id is of ProductTable)  
      
    FETCH NEXT FROM brand_cursor     
INTO @brand_id   
   
END     
CLOSE brand_cursor;    
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 a faster performance in case huge records need to be processed.

Conclusion

This article taught us the basics of cursors in SQL Server and how to work with cursors to fetch and manipulate data in a SQL Server database.