Different types of cursors in SQL server

Updated : Oct 10, 2019 in SQL server

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
Different types of cursors in SQL server
Different types of cursors in SQL server

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 

Latest posts by DuttaluruVijayakumar (see all)
Like
Like Love Haha Wow Sad Angry

Subscribe
Notify of
guest
1 Comment
Oldest
Newest Most Voted
Inline Feedbacks
View all comments
Free Stuff
January 29, 2020 5:26 am

WONDERFUL Post.thanks for share..more wait .. ?

1
0
Would love your thoughts, please comment.x
()
x