T-SQL: A Simple Example Using a Cursor

For more information on cursors, also take a look at the free SQL query training provided by Steve Stedman.

Cursors are a feature of SQL Server that allow users to iterate through a result set one row at a time. This can be useful in situations where a user needs to perform a set of operations on each row of a result set, such as updating or deleting data.

To use a cursor, a user must first declare it and define the result set that it will iterate through. The user can then open the cursor and begin iterating through the result set, performing the necessary operations on each row. Once the user has finished working with the cursor, they can close it to release the resources that it was using.

There are several types of cursors available in SQL Server, each with its own characteristics and uses. For example, static cursors store a copy of the result set in the tempdb database, which allows the user to move backwards and forwards through the result set. Dynamic cursors, on the other hand, do not store a copy of the result set, and only allow the user to move forward through it.

While cursors can be useful in some situations, they can also have some drawbacks. For example, because they operate on a row-by-row basis, they can be slower and less efficient than other methods of working with data in SQL Server. In addition, they can consume significant resources, such as memory and tempdb space, which can impact the overall performance of the server.

Overall, cursors are a useful feature of SQL Server that can allow users to iterate through a result set one row at a time. However, it is important to carefully consider their drawbacks and ensure that they are used in an efficient and resource-conscious manner.

In SQL Server the cursor is a tool that is used to iterate over a result set, or to loop through each row of a result set one row at a time. It may not be the best way to work with a set of data, but if you need to loop row by agonizing row (RBAR) in a T-SQL script then a cursor is one way of doing it.

Note: If you are new to SQL Server and come from an Oracle background, you should know that cursors on SQL Server are different from those on Oracle.

Before creating the cursor, we will just start with a simple query that will end up being used in the cursor.

USE AdventureWorks2008;
GO

SELECT BusinessEntityID, Name
 FROM Sales.Store;

Which looks something like this:

Now to convert it to a cursor, instead of just a select statement.

Step 1: Declare variables to hold the output from the cursor.

DECLARE @BusinessEntityID as INT;
DECLARE @BusinessName as NVARCHAR(50);

Step 2: Declare the cursor object;

DECLARE @BusinessCursor as CURSOR;

Step 3: Assign the query to the cursor.

SET @BusinessCursor = CURSOR FOR
SELECT BusinessEntityID, Name
 FROM Sales.Store;

Step 4: Open the cursor.

OPEN @BusinessCursor;

Step 5: Fetch the first row.

FETCH NEXT FROM @BusinessCursor INTO @BusinessEntityID, @BusinessName;

Step 6: Loop until there are no more results.  In the loop print out the ID and the name from the result set and fetch the net row.

WHILE @@FETCH_STATUS = 0
BEGIN
 PRINT cast(@BusinessEntityID as VARCHAR (50)) + ' ' + @BusinessName;
 FETCH NEXT FROM @BusinessCursor INTO @BusinessEntityID, @BusinessName;
END

Step 7: Close the cursor.

CLOSE @BusinessCursor;

Step 8: Deallocate the cursor to free up any memory or open result sets.

DEALLOCATE @BusinessCursor;

Now putting it all together:

DECLARE @BusinessEntityID as INT;
DECLARE @BusinessName as NVARCHAR(50);

DECLARE @BusinessCursor as CURSOR;

SET @BusinessCursor = CURSOR FOR

SELECT BusinessEntityID, Name
 FROM Sales.Store;

OPEN @BusinessCursor;
FETCH NEXT FROM @BusinessCursor INTO @BusinessEntityID, @BusinessName;

WHILE @@FETCH_STATUS = 0
BEGIN
 PRINT cast(@BusinessEntityID as VARCHAR (50)) + ' ' + @BusinessName;
 FETCH NEXT FROM @BusinessCursor INTO @BusinessEntityID, @BusinessName;
END

CLOSE @BusinessCursor;
DEALLOCATE @BusinessCursor;

This should give you a quick overview of how to quickly build and use a cursor on Microsoft SQL Server. The example shown was run on SQL Server 2008, and works the same on SQL Server 2005 , SQL Server 2008R2, SQL Server 2012 or SQL Server 2014.

Here is a video showing a similar overview of using cursors in TSQL.

Enjoy!

-Steve Stedman

Related Links

• Video: Simple Introduction to TSQL Cursors
• Free SQL Query Training for the 70-461 course
• Simple Cursor Example : FORWARD_ONLY vs FAST FORWARD
• Video Training on Using Cursors With SQL Server
• More details on Cursors
• Using a CURSOR to list databases
• Cursors – Technical debt
• Listing Database Example with a CURSOR

Have you tried Database Health Monitor?

More from Stedman Solutions:

Steve and the team at Stedman Solutions are here for all your SQL Server needs.
Contact us today for your free 30 minute consultation..
We are ready to help!