Updated : Sep 18, 2019 in SQL server

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 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

1 Comment

Leave a Reply

Your email address will not be published. Required fields are marked *