Cursor in SQL Server

WHAT IS CURSOR?
A cursor is a database object which is used to retrieve data from resultset one row at a time.The cursor can be used when the data needs to be updated row by row.

CURSOR LIFE CYCLE

Declaring Cursor
A cursor is declared by defining the SQL statement.

Opening Cursor
A cursor is opened for storing data retrieved from the result set.

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.

Closing Cursor
The cursor should be closed explicitly after data manipulation.

Deallocating Cursor
Cursors should be deallocated to delete cursor definition and released all the system resources associated with the cursor.

WHY USE CURSORS?

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

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.

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; 

Output


CURSOR REPLACEMENT IN SQL SERVER

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 type of problems, 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,

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;

Output

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

Output



Source : https://www.c-sharpcorner.com/article/cursors-in-sql-server/