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
- Static Cursors
- Dynamic Cursors
- 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
- How to create dynamic form fields using jQuery - January 4, 2021
- What is CTS (Common Type System) in .Net - October 16, 2020
- What is main method in c# - October 13, 2020
WONDERFUL Post.thanks for share..more wait .. ?