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.