T-SQL Tutorial

Cursors - TSQL Tutorial


In this section you can learn how to work with cursors using operations like declare cursor, create procedure, fetch, delete, update, close, set, deallocate. To create a cursor, use the CURSOR DECLARE syntax. When you declare a cursor, it is loaded with records, in order to access the records of the cursor it must be opened using OPEN and the rows are returned using FETCH.

Declare cursor

The T-SQL statement DECLARE CURSOR is used to create and declare cursors. In the declarative part, the cursor is created and loaded with values.

DECLARE cursor_name CURSOR
FOR sql_statement

Open cursor

The OPEN cursor statement opens a Transact-SQL server cursor.

OPEN cursor_name | cursor_variable_name

Fetch cursor

The FETCH cursor statement retrieves records from a Transact-SQL server cursor.

FETCH [ NEXT | PRIOR | FIRST | LAST ]
FROM cursor_name INTO cursor_variables;

Close cursor

The CLOSE cursor statement closes an open cursor by releasing the current result set. CLOSE leaves the records available in cursor and allows the cursor to be reopened.

CLOSE cursor_name | cursor_variable_name

Deallocate cursor

The DEALLOCATE cursor statement delete the cursor.

DEALLOCATE cursor_name | cursor_variable_name

Cursor example

In the example below, we will create a cursor by going through all the steps that make up the cursor.
In the declarative part we will DECLARE and load the cursor with records and we will also declare variables to use them to display the information.
Once the cursor is created, we will open it using the OPEN statement, then process the cursor using the FETCH statement and load variables with values.
CLOSE statement is used to close the cursor, then if it no longer needs to be opened it is destroyed with DEALLOCATEstatement.

USE model;
GO
DECLARE @id INT;
DECLARE @name VARCHAR(500);
DECLARE @price INT;
DECLARE course_cursor CURSOR FOR
SELECT id, name, price FROM courses;
OPEN course_cursor;
FETCH NEXT FROM course_cursor INTO @id, @name, @price;
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT CONCAT('id: ', @id, ', name: ', @name, ', price: ', @price);
FETCH NEXT FROM course_cursor INTO @id, @name, @price;
END;
CLOSE course_cursor;
DEALLOCATE course_cursor;
GO

Cursor operations