By: Simon Liew | Comments (1) | Related: 1 | 2 | 3 | 4 | 5 | 6 | > TSQL
Problem
Can you provide different examples of how a cursor can be written in SQL Server?
Solution
This tip provides 5 different examples of how a cursor can be written in SQL Server.
Each of the examples does not include the optional cursor type arguments in the declaration, such as whether the cursor is local, global, forward only, static and so on. The examples will focus on the different ways it can be written in SQL Server and some additional description of the structure.
Note, this tip does not describe the performance aspect of cursor usage.
Most Common SQL Server Cursor Syntax
Below is probably the most common way of writing a cursor that I have seen.
This is likely due to the cursor structure example provided in the official Microsoft documentation.
DECLARE
@database_id INT,
@database_name VARCHAR(255);
DECLARE cursor_db CURSOR
FOR SELECT
database_id, name
FROM sys.master_files;
OPEN cursor_db;
FETCH NEXT FROM cursor_db INTO @database_id, @database_name;
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT @database_name + ' id:' + CAST(@database_id AS VARCHAR(10));
FETCH NEXT FROM cursor_db INTO
@database_id,
@database_name;
END;
CLOSE cursor_db;
DEALLOCATE cursor_db;
In this structure, it is important that the cursor is closed and deallocated at the end of the cursor execution. This is essential in order to re-run the code in the same active session. Otherwise, a cursor already open error as shown below will prompt. Developers often encounter this error when some part of the code within the cursor failed during execution and the cursor is still open.
A cursor with the name 'cursor_db' already exists.
Msg 16905, Level 16, State 1, Line 10
The cursor is already open.
SQL Server Cursor as a Variable Example
A less common, but more effective way of writing a cursor is to declare the cursor as a variable.
As you probably aware, a variable scope is only active from the point when it is declared until the end of the batch execution. This applies to a cursor as a variable as well. So, when the batch completes (or errored) in the example below, the cursor variable will automatically get deallocated because the execution batch has ended.
This has an advantage where the developer does not get the cursor already open error even when there is an error encountered during the cursor execution and the execution did not reach the end of the code where the cursor is closed and deallocated.
DECLARE
@cursor_db CURSOR
DECLARE
@database_id INT,
@database_name VARCHAR(255);
SET @cursor_db = CURSOR
FOR SELECT
database_id, name
FROM sys.master_files;
OPEN @cursor_db;
FETCH NEXT FROM @cursor_db INTO
@database_id, @database_name;
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT @database_name + ' id:' + CAST(@database_id AS VARCHAR(10));
FETCH NEXT FROM @cursor_db INTO
@database_id,
@database_name;
END;
CLOSE @cursor_db;
DEALLOCATE @cursor_db;
SQL Server Cursor as Output of a Stored Procedure
A cursor data type can also be output of a SQL Server stored procedure.
The declaration of the cursor can be embedded into the body of a stored procedure. Then the cursor output from the stored procedure can be assigned just like any output of a stored procedure to the same data type. The cursor variable result set output can then be retrieved as per a normal cursor retrieval operation.
The advantage of this method is reusability of the code as the developer does not have to write the same cursor declaration in multiple places.
USE tempdb
GO
-- Cursor as an output of a stored procedure
CREATE PROCEDURE dbo.usp_cursor_db
@cursor_db CURSOR VARYING OUTPUT
AS
BEGIN
DECLARE
@database_id INT,
@database_name VARCHAR(255);
SET @cursor_db = CURSOR
FOR SELECT
database_id, name
FROM sys.master_files;
OPEN @cursor_db;
END
GO
-- Code to retrieve the cursor resultset output from the stored procedure
DECLARE
@cursor_db CURSOR
DECLARE
@database_id INT,
@database_name VARCHAR(255);
EXEC dbo.usp_cursor_db @cursor_db = @cursor_db OUTPUT
FETCH NEXT FROM @cursor_db INTO
@database_id, @database_name;
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT @database_name + ' id:' + CAST(@database_id AS VARCHAR(10));
FETCH NEXT FROM @cursor_db INTO
@database_id,
@database_name;
END;
CLOSE @cursor_db;
DEALLOCATE @cursor_db;
GO
SQL Server Cursor Current Of Example
The cursor example here is rarely used in T-SQL development. A more detailed description of the CURRENT OF clause can be found in the article UPDATE (Transact-SQL). This specifies that the update is performed at the current position of the specified cursor.
A positioned update using a WHERE CURRENT OF clause updates the single row at the current position of the cursor. This can be more accurate than a searched update that uses a WHERE <search_condition> clause to qualify the rows to be updated. A searched update modifies multiple rows when the search condition does not uniquely identify a single row.
We first prepare a temporary table using the SELECT INTO query as below. This temporary table will be used to illustrate the use of CURRENT OF to update the current row in the cursor example later.
USE tempdb
GO
SELECT
database_id, name
INTO #master_files
FROM sys.master_files
ORDER BY database_id, name;
We now implement a cursor structure using the CURRENT OF clause as below.
In this structure, the cursor will loop through each row retrieved from the SELECT query in the cursor declaration. The CURRENT OF clause allows an update or delete operation at the current position of the cursor without the need to specify a WHERE clause to qualify the row to be updated. As per the example code below, a developer can perform an update or delete directly on a row by row basis called within the cursor loop.
DECLARE
@cursor_db CURSOR
SET @cursor_db = CURSOR
FOR SELECT
database_id, name
FROM #master_files
WHERE database_id % 2 = 0;
OPEN @cursor_db;
FETCH NEXT FROM @cursor_db;
WHILE @@FETCH_STATUS = 0
BEGIN
-- Can also DELETE the current rows of @cursor_db cursor
/*
DELETE FROM #master_files
WHERE CURRENT OF @cursor_db;
*/
UPDATE #master_files SET [name] = [name] + CAST([database_id] AS VARCHAR(10))
WHERE CURRENT OF @cursor_db;
FETCH NEXT FROM @cursor_db;
END;
CLOSE @cursor_db;
DEALLOCATE @cursor_db;
SQL Server Cursor Alternative with a WHILE Loop
Now you might argue that the example below is not a cursor. While it does not have the DECLARE CURSOR for it to technically qualify as a cursor, it does function like a cursor.
I have seen developers replacing the DECLARE CURSOR structure with the code below and claim they have removed cursor usage from their code. Technically yes, but they have just replaced the code with an equivalent cursor like structure. It works just as good (or just as bad) as a cursor.
The advantage of using a WHILE loop is that no objects are required to be created in memory to facilitate the looping.
SELECT
idx = IDENTITY(INT, 1,1),
database_id, name
INTO #cursor
FROM sys.master_files;
DECLARE @i INT = 1, @database_id INT, @database_name NVARCHAR(256)
WHILE 1 = 1
BEGIN
SELECT @database_id = database_id, @database_name = name
FROM #cursor
WHERE idx = @i
IF @@ROWCOUNT = 0
BREAK;
PRINT @database_name + ' id:' + CAST(@database_id AS VARCHAR(10));
SET @i += 1
END
DROP TABLE #cursor
GO
The above code can be written several ways to exit the loop and I am just showing one way using the BREAK command when the SELECT statement returns no rows.
Summary
In this tip, we have covered different examples of how a cursor can be written in SQL Server. Because a cursor retrieves data one row at a time, it should be especially avoided when processing large volumes of transactions. Say each single row looping is “efficient” and completes in 1 millisecond, imagine the time it would take to loop over 10 million rows. This translates to 166 minutes and the processing time becomes volume bound. As the volume gets bigger, the processing time will take longer to complete. Now, the 1 millisecond per loop doesn’t look that efficient anymore.
Nevertheless, there are scenarios where a cursor is useful. This comes down to understanding when they should and when they shouldn’t be used.
Next Steps
- DECLARE CURSOR (Transact-SQL)
- DECLARE @local_variable (Transact-SQL)
- UPDATE (Transact-SQL)
- SQL Server Cursor Example
- SQL Server Loop through Table Rows without Cursor
About the author
Simon Liew is an independent SQL Server Consultant in Sydney, Australia. He is a Microsoft Certified Master for SQL Server 2008 and holds a Master’s Degree in Distributed Computing.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
