T-SQL: A Simple Example Using a Cursor

Download PDF

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:

SimpleCursor1

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;
SimpleCursor2

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.

cursorVideoScreenshot

Enjoy!

-Steve Stedman

Related Links

Have you tried Database Health Monitor?

 

More from Stedman Solutions:

SteveStedman5
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!

28 Comments on “T-SQL: A Simple Example Using a Cursor

  1. The question, though, is when would you want to use it? There are only a handful of cases where you’d need it, with even fewer cases on 2012. The main reason to avoid them is speed. Usually cursors are used by devs who don’t understand set-based queries, and in my experience a large set will run 10 times slower. Oracle lives on cursors, SQL Server punishes the queries for using them.

    Don’t get me wrong, good example, but overall it’s a Very Bad Idea, and you might preface it with that.
    TBD

  2. MBourgon… Yes I agree, most of the time cursors are a very bad ideas, even worse if they are nested. Thanks for the feedback, I will update the original post.

    -Steve Stedman

  3. I came up with a perfect time that cursors would need to be used. In the military we used the expression “having a man in the loop”. There are times where sensitive data needs to be reviewed by a human before being automatically updated via a stored procedure.

  4. If you really need to use CURSOR, a good practice is to first get your set data via CTE into a temp table, then run your cursor(s) over the temp table. That avoids the database having to take the cursor performance hit.

  5. Thank you for your great intro into cursors. I’ve tried to read some others – they were owfull – long and not clear at all. Thank you so much! You are the best.

  6. Was it intentional to have step 5’s in your example? Really learnt a lot from your example though – well explained

  7. Thank you ! It was very clear to understand Cursor from your example. Especially for a newbie who was just trying to figure out the syntax of cursor. One request : would it be possible for you to update this tutorial with information about how you set up cursor using SSMS ? like in my case, I need to check certain fields in a table everyday and if those fields have some specific values then I’ll shoot out an email from database.
    I am not worried about performance as ours is a small organization and table is not that big as well.

    Thanks a lot again.

  8. Agree with an earlier observation that typically you want to avoid cursors in favor of anything that is set based for performance reasons. However there are cases that need it. For example we have a source for our budgets where the budget categories get updated every year and the data comes to the warehouse from an excel spreadsheet in a form that has to be unpivoted. E.G. CostCenter, MonthYear, GrossRevenueAmount, NetRevenueAmount, ProfitAmount where the list of Amounts is changing on a yearly basis. The unpivot command has an IN clause that won’t accept a nested subselect so we had to use a cursor to build up the SQL Select dynamically.

    Code to do so was:

    declare @colname varchar(255);
    declare @startquery nvarchar(max);
    declare @endquery nvarchar(max);
    declare @fullquery nvarchar(max);
    set @startquery = ‘select [Practice], [dimDateKey], target_cat, [TargetMeasure]
    INTO ##base_target
    from [STG_CustomSource].[dbo].[Target] tgt
    unpivot
    (
    [TargetMeasure]
    for target_cat in (‘

    set @endquery = ‘ ) u;’

    declare @inclausetarget varchar(3000);
    declare @colsCursor as CURSOR;
    set @colsCursor = Cursor for
    select COLUMN_NAME
    from STG_CustomSource.INFORMATION_SCHEMA.COLUMNS
    where TABLE_NAME = ‘Target’
    and COLUMN_NAME NOT IN (‘Practice’, ‘BudgetVersion’,’dimDateKey’,’LoadCycleKey’,’LoadCreatedTS’,’LoadModifiedTS’)
    ;

    open @colsCursor;
    Fetch next from @colsCursor into @colname;
    set @inclausetarget = @colname
    Fetch next from @colsCursor into @colname;
    WHILE @@FETCH_STATUS = 0
    BEGIN
    set @inclausetarget = @inclausetarget + ‘, ‘ + @colname
    Fetch next from @colsCursor into @colname;
    END
    set @inclausetarget = @inclausetarget + ‘)’

    set @fullquery = @startquery + @inclausetarget + @endquery
    exec sp_executesql @fullquery

    resulting in a global temp table ##base_target with data from the unpivot. Note that a local temp table is unusable if you are using dynamic sql.

    • Robert, thanks for the comment.

      When you say that local temp tables are unusable, the reason is that the scope, or the session associated with the temp table when running the dynamic sql is different from the current session therefore you can’t see the temp table.

      Global temp tables can be dangerous since they are global, and other sessions could see and use them accidently.

      One way to do this would be to not use a temp table in the dynamic sql, and to just return a result set, then to insert the result set returned from the dynamic sql into a temp table,

      INSERT INTO #tempTableName EXEC sp_executeSQL @fullquery.

      Hope this helps!

      -Steve

  9. Steve,

    This article has been my companion since it was written. Before i start any new SQL project I have this page open for reference…..

    :)
    Thank you!

  10. Great and helpful article. But could I have a question? In case I’m using cursor within a SQL function, will CLOSE and DEALLOCATE always execute after the function return some value?

  11. Thanks for this. I’m in the process of reading through and recreating queries from old code to new data sources. Existing code uses a lot of cursors, and that’s not something I’ve worked with much. I appreciate the straight forward “It’s not really necessary” information here.

  12. Thanks Steve, very helpful and quick easy explanation. Would you say this technique is still appropriate at this time?

  13. Hi Steve,

    I’m not sure how many hits you get on this page per day, but for several years now, I google “tsql cursor Steve” a few times per week in order to get back here and remind myself of the exact syntax.
    I thought it was about time I said Thanks!

Leave a Reply

Your email address will not be published. Required fields are marked *

*