Cursors in SQL: Best Practices for Advanced Querying

TechDyer

An essential component of any database, cursors in SQL essentially make it easier for users to navigate around the database. I’ll be providing you with all the information you need to know before you start using Cursor in SQL through this article.

What are Cursors in SQL?

In SQL, a cursor enables you to traverse the rows of a result set and process individual rows returned by a query. It creates a temporary workspace or context area in memory during the execution of a SQL statement, storing data retrieved from the database for manipulation. Think of it as a structure containing rows with a pointer indicating the current row. While a cursor can hold multiple rows, it processes only one row at a time. The collection of rows held by a cursor is referred to as an active set, enabling you to manage table records one row at a time.

Cursor in SQL Server

A cursor is a database object in SQL Server that lets us retrieve and work with each row individually. A row pointer is all that a cursor is. In SQL, it is always used in conjunction with a SELECT command. Usually, a series of SQL instructions loops through a predetermined number of rows one at a time.

Life Cycle of Cursor

Declare Cursor: declares and links a specific SELECT statement to a cursor variable.

SQL Statement

DECLARE cursor_name CURSOR FOR

  SELECT column1, column2, …

  FROM table_name

  WHERE condition;

 

Open Cursor: enables the retrieval of rows by opening the cursor.

See also  What is Rest API in Java? A Beginner's Guide

SQL Statement

OPEN cursor_name;

 

Fetch Cursor: Brings up the next row in the result set selected by the cursor.

SQL Statement

FETCH cursor_name INTO variable1, variable2, …;

 

Close Cursor: The resources associated with the cursor are released when it is closed.

SQL Statement

CLOSE cursor_name;

 

Deallocate Cursor: The cursor definition is deleted, and its resources are released.

SQL Statement

DEALLOCATE cursor_name;

Types of Cursors in SQL Server

Static Cursors: For the duration of the cursor’s existence, SQL Server static cursors retrieve and store a snapshot of the result set at the time of cursor formation. The result set that the cursor displays is unaffected by modifications made to the underlying data after it is created.

 

Example 

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 

 

Dynamic Cursors: Because they dynamically update the result set in response to changes in the underlying data, SQL Server dynamic cursors offer greater flexibility. This type of cursor enables real-time updates due to the dynamic nature of the result set, but it may come with extra costs.

 

Example

–Dynamic Cursor for Update

SET NOCOUNT ON

DECLARE @Id int

DECLARE @name varchar(50)

 DECLARE Dynamic_cur_empupdate CURSOR

DYNAMIC 

FOR 

SELECT EmpID, EmpName from Employee ORDER BY EmpName

See also  Unraveling Inheritance in CSS: Mastering the Cascade

OPEN Dynamic_cur_empupdate

IF @@CURSOR_ROWS > 0

 BEGIN 

 FETCH NEXT FROM Dynamic_cur_empupdate INTO @Id,@name

 WHILE @@Fetch_status = 0

 BEGIN

 IF @name=’Mohan’

 Update Employee SET Salary=15000 WHERE CURRENT OF Dynamic_cur_empupdate

 FETCH NEXT FROM Dynamic_cur_empupdate INTO @Id,@name

 END

END

CLOSE Dynamic_cur_empupdate

DEALLOCATE Dynamic_cur_empupdate

SET NOCOUNT OFF

 Go

Select * from Employee 

 

Forward-Only Cursors: SQL Server’s forward-only cursors allow only forward traversal of the result set. They are designed for forward scrolling, which makes them more efficient for one-way data access, and uses fewer resources than other cursor types.

 

Example 

–Forward Only Cursor for Update

SET NOCOUNT ON

DECLARE @Id int

DECLARE @name varchar(50)

 DECLARE Forward_cur_empupdate CURSOR

FORWARD_ONLY

FOR 

SELECT EmpID, EmpName from Employee ORDER BY EmpName

OPEN Forward_cur_empupdate

IF @@CURSOR_ROWS > 0

 BEGIN 

 FETCH NEXT FROM Forward_cur_empupdate INTO @Id,@name

 WHILE @@Fetch_status = 0

 BEGIN

 IF @name=’Amit’

 Update Employee SET Salary=24000 WHERE CURRENT OF Forward_cur_empupdate

 FETCH NEXT FROM Forward_cur_empupdate INTO @Id,@name

 END

END

CLOSE Forward_cur_empupdate

DEALLOCATE Forward_cur_empupdate

SET NOCOUNT OFF

 Go

Select * from Employee 

 

Keyset Cursors: Keyset cursors in SQL Server maintain a static set of SQL keys from the result set, allowing modifications to the data but preventing modifications to the key values. This allows for quick navigation and change by striking a balance between the fixed nature of static cursors and the dynamic adaptability of dynamic cursors.

 

Example 

— Keyset driven Cursor for Update

SET NOCOUNT ON

DECLARE @Id int

DECLARE @name varchar(50)

 DECLARE Keyset_cur_empupdate CURSOR

KEYSET

FOR 

SELECT EmpID, EmpName from Employee ORDER BY EmpName

OPEN Keyset_cur_empupdate

IF @@CURSOR_ROWS > 0

 BEGIN 

 FETCH NEXT FROM Keyset_cur_empupdate INTO @Id,@name

See also  LLM for Coding: A Comprehensive Guide

 WHILE @@Fetch_status = 0

 BEGIN

 IF @name=’Pavan’

 Update Employee SET Salary=27000 WHERE CURRENT OF Keyset_cur_empupdate

 FETCH NEXT FROM Keyset_cur_empupdate INTO @Id,@name

 END

END

CLOSE Keyset_cur_empupdate

DEALLOCATE Keyset_cur_empupdate

SET NOCOUNT OFF

 Go

Select * from Employee 

FAQ

Q1. What does a cursor look like?

Ans. Cursors are commonly used to highlight text or other items on the screen so that you can click on them to select them. For instance, the cursor can be used to select, format, and add new text in a word processor. Users manipulate cursors with the help of input devices like trackballs, touchpads, and mice.

Q2. What are the cursor attributes in SQL Server?

Ans. A set of properties is present in every cursor that enables an application program to assess the cursor’s state. The attributes %ISOPEN, %FOUND, %NOTFOUND, and %ROWCOUNT are as follows. This feature is employed to determine if a cursor is open or closed.

Q3. Which is referred to as a cursor.

Ans. On a computer display screen, a cursor is an indicator that shows the user’s text entry area. A mouse, touchpad, or other comparable input device can be used to control the cursor, which is also a visible and moving pointer in an operating system with a graphical user interface (GUI).

Read more

Share This Article
Follow:
I'm a tech enthusiast and content writer at TechDyer.com. With a passion for simplifying complex tech concepts, delivers engaging content to readers. Follow for insightful updates on the latest in technology.
Leave a comment

Leave a Reply

Your email address will not be published. Required fields are marked *