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
Let's Work with Example
sqlCREATE 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
sqlSET 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
Copyrights © 2024 letsupdateskills All rights reserved