Cursors In SQL Server

Life Cycle of Cursor

  • Declare Cursor
  1. A cursor is declared by defining the SQL statement that returns a result set.
  • Open
  1. A Cursor is opened and populated by executing the SQL statement defined by the cursor.
  • Fetch
  1. When the cursor is opened, rows can be fetched from the cursor one by one or in a block to do data manipulation.
  • Close
  1. After data manipulation, we should close the cursor explicitly.
  • Deallocate
  1. Finally, we need to delete the cursor definition and released all the system resources associated with the cursor.

Syntax to Declare Cursor

Declare Cursor SQL Command is used to define the cursor with many options that impact the scalability and loading behavior of the cursor. The basic syntax is given below

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

Syntax to Open Cursor

A Cursor can be opened locally or globally. By default, it is opened locally. The basic syntax to open cursor is given below:

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

Syntax to Fetch Cursor

Fetch statement provides the many options to retrieve the rows from the cursor. NEXT is the default option. The basic syntax to fetch cursor is given below:

FETCH [NEXT|PRIOR|FIRST|LAST|ABSOLUTE n|RELATIVE n] FROM [GLOBAL] cursor_name INTO @Variable_name[1,2,..n]

Syntax to Close Cursor

The close statement closed the cursor explicitly. The basic syntax to close cursor is given below:

CLOSE cursor_name --after closing it can be reopen

Syntax to Deallocate Cursor

Deallocate statement delete the cursor definition and free all the system resources associated with the cursor. The basic syntax to close cursor is given below:

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

SQL SERVER — Simple Examples of Cursors

CREATE TABLE Employee ( EmpID int PRIMARY KEY, EmpName varchar (50) NOT NULL, Salary int NOT NULL, Address varchar (200) NOT NULL, ) GO INSERT INTO Employee(EmpID,EmpName,Salary,Address) VALUES(1,'Mohan',12000,'Noida') INSERT INTO Employee(EmpID,EmpName,Salary,Address) VALUES(2,'Pavan',25000,'Delhi') INSERT INTO Employee(EmpID,EmpName,Salary,Address) VALUES(3,'Amit',22000,'Dehradun') INSERT INTO Employee(EmpID,EmpName,Salary,Address) VALUES(4,'Sonu',22000,'Noida') INSERT INTO Employee(EmpID,EmpName,Salary,Address) VALUES(5,'Deepak',28000,'Gurgaon') GO SELECT * FROM Employee
SET NOCOUNT ON DECLARE @Id int DECLARE @name varchar(50) DECLARE @salary int DECLARE cur_emp CURSOR STATIC FOR SELECT EmpID,EmpName,Salary from Employee OPEN cur_emp IF @@CURSOR_ROWS > 0 BEGIN FETCH NEXT FROM cur_emp INTO @Id,@name,@salary WHILE @@Fetch_status = 0 BEGIN PRINT 'ID : '+ convert(varchar(20),@Id)+', Name : '+@name+ ', Salary : '+convert(varchar(20),@salary) FETCH NEXT FROM cur_emp INTO @Id,@name,@salary END END CLOSE cur_emp DEALLOCATE cur_emp SET NOCOUNT OFF

--

--

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store
Bhavik Kumar

Bhavik Kumar

Bhavik is from India. He currently works as a SEO Executive. He has been taught the first two lessons so far, and accepted everything in those lessons. He felt