Different types of cursors in SQL server

this article we will discuss different types of cursors in SQL Server with examples

What is a Cursor in SQL SERVER?

A cursor in SQL Server is a database object.

cursors are used to retrieve data through a SELECT statement returns a set of rows which is called a “result set “ result set one row at a time.

Cursor in SQL Server probably use a loop like FOR or WHILE to iterate through one item at a time in In T-SQL(Insert, Update And Delete)

Different types of cursors in SQL server

1. Static Cursors
2. Dynamic Cursors
3. Forward Only Cursors

Cursors Examples in SQL server

Create a table by using the following given Query

  
CREATE TABLE Products
(
 Id int PRIMARY KEY,
 ProductName varchar (50) NOT NULL,
 SupplierId int NOT NULL,
 UnitPrice decimal NOT NULL,
 IsDiscontinued bit NOT NULL
)
GO
INSERT INTO Products(Id,ProductName,SupplierId,UnitPrice,IsDiscontinued) VALUES(1,'Soney-Tv',3,12000,0)
INSERT INTO Products(Id,ProductName,SupplierId,UnitPrice,IsDiscontinued) VALUES(2,'LG-Tv',4,25000,1)
INSERT INTO Products(Id,ProductName,SupplierId,UnitPrice,IsDiscontinued) VALUES(3,'vevo-Tv',5,22000,0)
INSERT INTO Products(Id,ProductName,SupplierId,UnitPrice,IsDiscontinued) VALUES(4,'Bpl-Tv',6,22000,1)
INSERT INTO Products(Id,ProductName,SupplierId,UnitPrice,IsDiscontinued) VALUES(5,'Ego-Tv',7,28000,0)
INSERT INTO Products(Id,ProductName,SupplierId,UnitPrice,IsDiscontinued) VALUES(6,'samsung-Tv',8,22000,1)
INSERT INTO Products(Id,ProductName,SupplierId,UnitPrice,IsDiscontinued) VALUES(7,'MI-Tv',9,28000,0)
GO
SELECT * FROM Products 

Static Cursor in SQL Server Example

in SQL server static cursors make a copy of the result set and then don’t reflect any changes if made to original base tables data such as T-SQL(Insert, Update, Delete)

By default in SQL Server, static cursors are scrollable and always read-only

SET NOCOUNT ON

DECLARE @Id int
DECLARE @ProductName varchar(50)
DECLARE @UnitPrice decimal
declare @IsDiscontinued bit

DECLARE cur_products CURSOR STATIC FOR 
SELECT Id,ProductName,UnitPrice from products

OPEN cur_products

IF @@CURSOR_ROWS > 0
 BEGIN 

 FETCH NEXT FROM cur_products INTO @Id,@ProductName,@UnitPrice
   print '--------- Products Details ---------'
 WHILE @@Fetch_status = 0
 BEGIN
 
 PRINT 'Id : '+ convert(varchar(20),@Id)+', Product Name : '[email protected]+ ', UnitPrice : '+convert(varchar(20),@UnitPrice)
 
 FETCH NEXT FROM cur_products INTO @Id,@ProductName,@UnitPrice

 END
END
CLOSE cur_products
DEALLOCATE cur_products
SET NOCOUNT OFF

Dynamic Cursor in SQL Server Example

Dynamic cursors allow reflecting any changes if made to original base tables data such as T-SQL(Insert, Update, Delete)

By default dynamic cursors are scrollable

--Dynamic Cursor for Update
SET NOCOUNT ON

DECLARE @Id int
DECLARE @ProductName varchar(50)

 DECLARE Dynamic_cur_products CURSOR DYNAMIC  FOR 
SELECT Id,ProductName from Products ORDER BY ProductName

OPEN Dynamic_cur_products

IF @@CURSOR_ROWS > 0
 BEGIN 

 FETCH NEXT FROM Dynamic_cur_products INTO @Id,@ProductName

 WHILE @@Fetch_status = 0
 BEGIN

 IF @ProductName='Soney-Tv'
 Update Products SET UnitPrice=155000 WHERE CURRENT OF Dynamic_cur_products

 FETCH NEXT FROM Dynamic_cur_products INTO @Id,@ProductName

 END
END
CLOSE Dynamic_cur_products
DEALLOCATE Dynamic_cur_products
SET NOCOUNT OFF
 Go
Select * from Products 

Forward Only Cursor in SQL Server Example

In SQL server forward-only cursors we are called fastest cursors among all cursors but these cursors are doesn’t support backward scrolling.

you can perform update, delete data using a forward-only cursor and we have three types of forward-only cursors KEYSET, FORWARD_ONLY STATIC, and FAST_FORWARD.

--Forward Only Cursor for Update
SET NOCOUNT ON

DECLARE @Id int
DECLARE @ProductName varchar(50)

 DECLARE Forward_cur_products CURSOR FORWARD_ONLY  FOR 
SELECT Id,ProductName from Products ORDER BY ProductName

OPEN Forward_cur_products

IF @@CURSOR_ROWS > 0
 BEGIN 

 FETCH NEXT FROM Forward_cur_products INTO @Id,@ProductName

 WHILE @@Fetch_status = 0
 BEGIN

 IF @ProductName='Soney-Tv'
 Update Products SET UnitPrice=1565000 WHERE CURRENT OF Forward_cur_products

 FETCH NEXT FROM Forward_cur_products INTO @Id,@ProductName

 END
END
CLOSE Forward_cur_products
DEALLOCATE Forward_cur_products
SET NOCOUNT OFF
 Go
Select * from Products