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 <br /> @database_id INT, <br /> @database_name VARCHAR(255);
<br />
DECLARE cursor_db CURSOR<br />FOR SELECT <br /> database_id, name<br /> FROM sys.master_files;<br /><br />OPEN cursor_db;<br /><br />FETCH NEXT FROM cursor_db INTO
@database_id, @database_name;<br /><br />WHILE @@FETCH_STATUS = 0<br />BEGIN<br /> PRINT @database_name + ' id:' + CAST(@database_id AS VARCHAR(10));<br /><br /> FETCH NEXT FROM cursor_db INTO <br /> @database_id, <br /> @database_name;<br />END;<br /><br />CLOSE cursor_db;<br /><br />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 <br /> @cursor_db CURSOR<br /><br />DECLARE<br /> @database_id INT, <br /> @database_name VARCHAR(255);<br /><br />SET @cursor_db = CURSOR<br />FOR SELECT <br /> database_id, name<br /> FROM sys.master_files;<br /><br />OPEN @cursor_db;<br /><br />FETCH NEXT FROM @cursor_db INTO <br /> @database_id, @database_name;<br /><br />WHILE @@FETCH_STATUS = 0<br />BEGIN<br /> PRINT @database_name + ' id:' + CAST(@database_id AS VARCHAR(10));<br /><br /> FETCH NEXT FROM @cursor_db INTO <br /> @database_id, <br /> @database_name;<br />END;<br /><br />CLOSE @cursor_db;<br /><br />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<br />GO
<br />-- Cursor as an output of a stored procedure<br />CREATE PROCEDURE dbo.usp_cursor_db<br />@cursor_db CURSOR VARYING OUTPUT<br />AS<br />BEGIN<br /> DECLARE<br /> @database_id INT, <br /> @database_name VARCHAR(255);<br /><br /> SET @cursor_db = CURSOR<br /> FOR SELECT <br /> database_id, name<br /> FROM sys.master_files;<br /><br /> OPEN @cursor_db;<br /><br />END<br />GO<br /><br />-- Code to retrieve the cursor resultset output from the stored procedure<br />DECLARE <br /> @cursor_db CURSOR<br /><br />DECLARE<br /> @database_id INT, <br /> @database_name VARCHAR(255);<br /><br />EXEC dbo.usp_cursor_db @cursor_db = @cursor_db OUTPUT<br /><br />FETCH NEXT FROM @cursor_db INTO <br /> @database_id, @database_name;<br /><br />WHILE @@FETCH_STATUS = 0<br />BEGIN<br /> PRINT @database_name + ' id:' + CAST(@database_id AS VARCHAR(10));<br /><br /> FETCH NEXT FROM @cursor_db INTO <br /> @database_id, <br /> @database_name;<br />END;<br /><br />CLOSE @cursor_db;<br /><br />DEALLOCATE @cursor_db;<br />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<br />GO<br />SELECT <br /> database_id, name<br />INTO #master_files<br />FROM sys.master_files<br />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 <br /> @cursor_db CURSOR<br /><br />SET @cursor_db = CURSOR<br />FOR SELECT <br /> database_id, name<br /> FROM #master_files<br /> WHERE database_id % 2 = 0;<br /><br />OPEN @cursor_db;<br /><br />FETCH NEXT FROM @cursor_db;<br /><br />WHILE @@FETCH_STATUS = 0<br />BEGIN<br /> -- Can also DELETE the current rows of @cursor_db cursor<br /> /*<br /> DELETE FROM #master_files<br /> WHERE CURRENT OF @cursor_db; <br /> */<br /><br /> UPDATE #master_files SET [name] = [name] + CAST([database_id] AS VARCHAR(10))<br /> WHERE CURRENT OF @cursor_db; <br /><br /> FETCH NEXT FROM @cursor_db;<br />END;<br /><br />CLOSE @cursor_db;<br /><br />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 <br /> idx = IDENTITY(INT, 1,1), <br /> database_id,
name<br />INTO #cursor<br />FROM sys.master_files;<br /><br />DECLARE @i INT = 1, @database_id INT, @database_name NVARCHAR(256)<br /><br />WHILE 1 = 1<br />BEGIN<br /> SELECT @database_id = database_id, @database_name = name<br /> FROM #cursor<br /> WHERE idx = @i<br /><br /> IF @@ROWCOUNT = 0<br /> BREAK;<br /><br /> PRINT @database_name + ' id:' + CAST(@database_id AS VARCHAR(10));<br /><br /> SET @i += 1 <br />END<br /><br />DROP TABLE #cursor<br />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
