Simple Example of Cursor in SQL Server

Hello Friends, In this tutorial we are going to see a simple example of Cursor in SQL Server. A cursor is a database object that allows us to process each row from a result set and manipulate its data.

What is Cursor?

A cursor is database object that allows us to process each row as a result and manipulate its data. A cursor is always associated with the select command that processes one row at a time returned by the select query. We use Cursor when we need to modify one row at a time returned by the select query.

The life cycle of the cursor:

Declare Cursor

A cursor is declared by defining the SQL statement that returns a result set.

Syntax to declare a cursor:

 DECLARE cursor_name CURSOR

 [LOCAL | GLOBAL] --define cursor scope

 [FORWARD_ONLY | SCROLL] --define cursor movements (forward/backward)

 [STATIC | KEYSET | DYNAMIC | FAST_FORWARD] --basic type of cursor

 [READ_ONLY | SCROLL_LOCKS | OPTIMISTIC] --define locks

 FOR select_statement --define SQL Select statement

 FOR UPDATE [col1,col2,...coln] --define columns that need to be updated

Open:

A Cursor is opened and populated by executing the SQL statement defined by the cursor.

Syntax to open a cursor:

OPEN [GLOBAL] cursor_name --by default it is local

Fetch

When a cursor is opened, rows can be fetched from the cursor one by one or in a block to do data manipulation.

Syntax to Fetch a cursor:

FETCH [NEXT|PRIOR|FIRST|LAST|ABSOLUTE n|RELATIVE n]

FROM [GLOBAL] cursor_name

INTO @Variable_name[1,2,..n]

Close

After data manipulation, we should close the cursor explicitly.

Syntax to Close a cursor:

CLOSE cursor_name

Deallocate

Finally, we need to delete the cursor definition and released all the system resources associated with the cursor.

Syntax to Deallocate a cursor:

DEALLOCATE cursor_name --after deallocation, it can't be reopen

Example of Cursor:

Create Database:

CREATE DATABASE DemoCursor

Use Database:

USE DemoCursor

Create Table:

CREATE TABLE [dbo].[Employee](
[EmployeeID] [bigint] IDENTITY(1,1) NOT NULL,
[EmployeeName] [varchar](100) NULL,
[Designation] [varchar](50) NULL,
[Salary] [float] NULL
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

Insert Records:

SET IDENTITY_INSERT [dbo].[Employee] ON

GO
INSERT [dbo].[Employee] ([EmployeeID], [EmployeeName], [Designation], [Salary]) VALUES (1, N'Ahwin Roy', N'Software Engineer', 800000)
GO
INSERT [dbo].[Employee] ([EmployeeID], [EmployeeName], [Designation], [Salary]) VALUES (2, N'Ritesh Shah', N'Team Leader', 900000)
GO
INSERT [dbo].[Employee] ([EmployeeID], [EmployeeName], [Designation], [Salary]) VALUES (3, N'Vikas Gupta', N'DBA', 1200000)
GO
INSERT [dbo].[Employee] ([EmployeeID], [EmployeeName], [Designation], [Salary]) VALUES (4, N'Akash Tripathi', N'Tester', 700000)
GO
INSERT [dbo].[Employee] ([EmployeeID], [EmployeeName], [Designation], [Salary]) VALUES (5, N'Nitin Pandey', N'Web Developer', 8000000)
GO
SET IDENTITY_INSERT [dbo].[Employee] OFF
GO

Create Cursor:

SET NOCOUNT ON
DECLARE @EmployeeID BIGINT
DECLARE @EmployeeName varchar(100)
DECLARE @Salary Float
DECLARE curEmployee CURSOR
STATIC FOR 
SELECT EmployeeID,EmployeeName,Salary from Employee
OPEN curEmployee
IF @@CURSOR_ROWS > 0
BEGIN 
FETCH NEXT FROM curEmployee INTO @EmployeeID,@EmployeeName,@Salary
WHILE @@Fetch_status = 0
BEGIN
PRINT 'ID : '+ convert(varchar(20),@EmployeeID)+', EmployeecName : '+@EmployeeName+ ', Salary : '+convert(varchar(20),@Salary)
FETCH NEXT FROM curEmployee INTO @EmployeeID,@EmployeeName,@Salary
END
END
CLOSE curEmployee
DEALLOCATE curEmployee
SET NOCOUNT OFF

Output:

View More:

Paging in SQL Server.
SQL Interview Questions and Answers.
Check Constraint in SQL Server.
Stored Procedure in SQL Server.

Conclusion:

I hope this post is useful for you. I would appreciate your feedback, comments, and suggestions.

Thank You.

Debug On Web