Updated : Sep 18, 2019 in SQL server

What is Cursor in SQL Server with example

In this article, we will learn about how to create a Cursor in SQL Server with example and Different Types of cursors in SQL server

what we will learn in this topic:

  • what is cursor in SQL server
  • cursor in SQL Server with example
  • why we use cursor in SQL server
  • how to avoid cursor in SQL server

What is Cursor in SQL SERVER?

A cursor in SQL Server is a database object

It is 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)

Cursor Syntax in SQL Server:

  • Declaring Cursor: declared by defining the SQL statement.
  • Opening the Cursor: for storing data retrieved from the result set.
  • Fetching Cursor: rows can be fetched from the cursor one by one or in a block
  • Closing Cursor : should be closed explicitly after data manipulation.
  • Deallocating Cursor: deletes cursor definition and releases all the system resources associated with the cursor.
Cursor Syntax in SQL Server:
	DECLARE cursor_name CURSOR [ LOCAL | GLOBAL ]   
	[ FORWARD_ONLY | SCROLL ]  
	 [ STATIC | KEYSET | DYNAMIC | FAST_FORWARD ]   
	[ READ_ONLY | SCROLL_LOCKS | OPTIMISTIC ]   
	[ TYPE_WARNING ] FOR select_statement  
	 [ FOR UPDATE [ OF column_name [ ,...n ] ] ] [;]   

Cursor in SQL Server with example

Create A Table following the given SQL query in SQL server

CREATE TABLE [dbo].[Product](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[ProductName] [nvarchar](50) NOT NULL,
	[SupplierId] [int] NOT NULL,
	[UnitPrice] [decimal](12, 2) NULL,
	[IsDiscontinued] [bit] NOT NULL,
 )
 -- Execute given Below Statements
--INSERT INTO [Product] ([ProductName],[SupplierId],[UnitPrice],[IsDiscontinued])VALUES('Uncle Bob''s Organic Dried Pears',3,30.00,0)
--INSERT INTO [Product] ([ProductName],[SupplierId],[UnitPrice],[IsDiscontinued])VALUES('Northwoods Cranberry Sauce',3,40.00,0)
--INSERT INTO [Product] ([ProductName],[SupplierId],[UnitPrice],[IsDiscontinued])VALUES('Mishi Kobe Niku',4,97.00,1)
--INSERT INTO [Product] ([ProductName],[SupplierId],[UnitPrice],[IsDiscontinued])VALUES('Ikura',4,31.00,0)

The given cursor is defined for retrieving product_id and  Product_Name  from the Product table

FETCH_STATUS =0 until no rows, all rows are fetched then FETCH_STATUS will become 1.

use  Dofactsample  
	SET NOCOUNT ON;    
	DECLARE @Id int ,@ProductName  varchar(20);    
	PRINT '-------- PRINT PRODUCT DETAILS --------';    
	DECLARE Product_cursor CURSOR FOR     
	SELECT Id,ProductName FROM Product order by Id;    
	  
	OPEN product_cursor    
	 FETCH NEXT FROM product_cursor     
	INTO @Id,@ProductName    
	Print 'product_id  product_Name'       
	WHILE @@FETCH_STATUS = 0    
	BEGIN    
	    print '   ' + CAST(@Id as varchar(10)) +'     '+  cast(@ProductName as varchar(20))  
	FETCH NEXT FROM product_cursor     
	INTO @Id,@ProductName    
	   
	END     
	CLOSE product_cursor;    
	DEALLOCATE product_cursor;    



Execute the Cursor you will get the output

Cursors in SQL
Cursors in SQL

why we use cursor in SQL server

cursors in SQL Server is a database object that can retrieve data from the result set one row at a time, courser used when we want to update data row by row in a database table

how to avoid cursor in SQL server

Cursors are one of an important topic in SQL server but it will affect the performance, it will consume instance memory instead of using cursors we will use WHILE loop, temporary table, temp variable for more Read

Like
Like Love Haha Wow Sad Angry
2

Subscribe
Notify of
guest
2 Comments
Oldest
Newest Most Voted
Inline Feedbacks
View all comments
Chere Mckenley
February 23, 2020 8:52 pm

Good post! Thanks!

Paz Hennig
April 9, 2020 1:59 pm

Bookmarked!, I enjoy it!

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