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.

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

 

 

 

Tagged with: , , , , , , ,
15 comments on “T-SQL: A Simple Example Using a Cursor
  1. Mbourgon says:

    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. SteveStedman SteveStedman says:

    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. John Glick says:

    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. Chris Prepress says:

    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. Ellen says:

    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. Oleg Glazirin says:

    Excellent explanation! Thank you!

  7. Tarek Khalifa says:

    Thank you, v.good explanation for cursor

  8. Aris says:

    T_H_A_N_K Y_O_U!
    You saved me!

  9. H says:

    Thank you! You made it very easy to understand

  10. Mohammad Shafiqur Rahman says:

    Thanks a lot. Its really helpful.

  11. Warren says:

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

  12. Ujjwal Garad says:

    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.

  13. Robert Cassis says:

    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.

    • SteveStedman SteveStedman says:

      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

Leave a Reply to Aris Cancel reply

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

*

Time limit is exhausted. Please reload CAPTCHA.