SQL Server Cursors

Level : Advanced

Mentor: Shailendra Chauhan

Duration : 00:01:00

Transact-SQL Cursors

Transact-SQL (T-SQL) cursors are used in SQL Server to iterate through result sets row by row. They provide fine-grained control over data manipulation and retrieval.

Example

DECLARE @EmployeeName NVARCHAR(50)
DECLARE cursor_employee CURSOR FOR
SELECT EmployeeName FROM Employees
OPEN cursor_employee
FETCH NEXT FROM cursor_employee INTO @EmployeeName
WHILE @@FETCH_STATUS = 0
BEGIN
  PRINT @EmployeeName
  FETCH NEXT FROM cursor_employee INTO @EmployeeName
END
CLOSE cursor_employee
DEALLOCATE cursor_employee

Client Cursors

Client cursors are cursors that are managed on the client-side, where the client application is responsible for cursor control and data retrieval.

Forward-only Cursor

Suitable for one-time read-only traversal of result sets.

Example

DECLARE cursor_forward CURSOR FORWARD_ONLY FOR
SELECT ProductName FROM Products

Static Cursor

Represents a snapshot of data, and changes made by other users aren't visible.

Example

DECLARE cursor_static CURSOR STATIC FOR
SELECT CustomerName FROM Customers

Keyset Cursor

Holds a unique identifier for each row, making it useful when you need to scroll through data efficiently.

Example

DECLARE cursor_keyset CURSOR KEYSET FOR
SELECT OrderID FROM Orders

Dynamic Cursor

Reflects all data changes made by other users during cursor navigation.

Example

DECLARE cursor_dynamic CURSOR DYNAMIC FOR
SELECT SalesAmount FROM SalesData

Requesting a Cursor

To request a cursor in SQL Server, you typically use the DECLARE CURSOR statement followed by the cursor definition and data retrieval queries.

Example

DECLARE cursor_example CURSOR FOR
SELECT ProductName, UnitPrice FROM Products

Cursor Process

The process of using a cursor in SQL Server typically involves declaring the cursor, opening it, fetching rows one by one, and then closing and deallocating the cursor.

Example

DECLARE cursor_process CURSOR FOR
SELECT CustomerName, OrderAmount FROM Orders
OPEN cursor_process
FETCH NEXT FROM cursor_process INTO @CustomerName, @OrderAmount
WHILE @@FETCH_STATUS = 0
BEGIN
  -- Process data here
  FETCH NEXT FROM cursor_process INTO @CustomerName, @OrderAmount
END
CLOSE cursor_process
DEALLOCATE cursor_process

Self-paced Membership

Upto 60% OFF

Know More

Still have some questions? Let's discuss.