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
I like to share my work experience, research, and knowledge through my blog.
I love developing the applications in Microsoft technologies including asp.net mvc,c#.net, windfarms, SQL server,entityfrawork,Ajax,Jquery,WEbApi,Asp.net core, SEO, WordPress, UI..more
- how to create ASP.NET Core 3 Web API Project - January 21, 2022
- JWT Authentication using OAUTH - January 10, 2022
- Ado.net database Transactions - January 9, 2022
WONDERFUL Post.thanks for share..more wait .. ?