Cursors in SQL Server are useful in scenarios where row-by-row processing is required.

Cursor is a database object that allows us to retrieve and manipulate each row one at a time.

Steps to Use a Cursor


  1. Declare the Cursor: Define the cursor and the SQL query it will use.
  2. Open the Cursor: Initialize the cursor and populate it with the result set.
  3. Fetch the Data: Retrieve rows one by one from the cursor.
  4. Process the Data: Perform the required operations on each row.
  5. Close the Cursor: Release the cursor's current result set and any locks it holds.
  6. Deallocate the Cursor: Free the resources associated with the cursor.


Let's Work with Example


sql
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,'Devesh',112000, 'Meerut') INSERT INTO Employee(EmpID,EmpName,Salary,Address) VALUES(2,'Pankaj',215000,'Delhi') INSERT INTO Employee(EmpID,EmpName,Salary,Address) VALUES(3,'Snjay',188965,'Kanpur') INSERT INTO Employee(EmpID,EmpName,Salary,Address) VALUES(4,'Yash',222000,'Noida') INSERT INTO Employee(EmpID,EmpName,Salary,Address) VALUES(5,'Devesh kuamr',283000,'Hapur') GO SELECT * FROM Employee



Example of Cursor




sql
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 PRINT 'ROW by ROW Execution start ........' IF @@CURSOR_ROWS > 0 BEGIN FETCH NEXT FROM cur_emp INTO @Id,@name,@salary WHILE @@Fetch_status = 0 BEGIN PRINT 'ID of Emp-> '+ convert(varchar(20),@Id)+', Name Of Emp -> '+@name+ ', Salary Of Emp -> '+convert(varchar(20),@salary) FETCH NEXT FROM cur_emp INTO @Id,@name,@salary END END CLOSE cur_emp DEALLOCATE cur_emp SET NOCOUNT OFF




Detailed Explanation


1. Declare Cursor:

DECLARE cur_emp CURSOR FOR defines the cursor and the SQL query it will use. This query selects the EmpID,EmpName,Salary from Emp Table

2. Open Cursor:

OPEN cur_emp; initializes the cursor and loads it with the result set of the query.

3. Fetch Data:

FETCH NEXT FROM cur_emp INTO @Id,@name,@salary fetches the first row of data from the cursor into the declared variables.

4. Process Data: We can apply Logic what ever we want

5. Fetch Next Row: 

FETCH NEXT FROM cur_emp INTO @Id,@name,@salary retrieves the next row from the cursor and updates the variables.

it will fetch all rows one by one till all row processed 

6. Close and Deallocate Cursor:

CLOSE cur_emp; releases the current result set and any locks held by the cursor.

DEALLOCATE cur_emp ; removes the cursor definition and frees the resources.


Thanks

line

Copyrights © 2024 letsupdateskills All rights reserved