Category: SQL

Knowing when to use cursors and when to avoid them is key to being a successful developer. Here’s a very useful snippet to use when you want to use a cursorless iteration over a resultset. In the example I create a temp table of widgets and then enumerate each row to print them out:

set nocount on;
  
declare @i int
declare @curr_widget nvarchar(20)
   
declare @widget_table TABLE (
    id smallint primary key identity(1,1)
    , widget_color nvarchar(10)
    , widget_type nvarchar(10)
 )
   
 -- populate the widget table with records
 insert @widget_table (widget_color, widget_type) values ('Red','Widget')
 insert @widget_table (widget_color, widget_type) values ('Orange','Gear')
 insert @widget_table (widget_color, widget_type) values ('Yellow','Rotor')
 insert @widget_table (widget_color, widget_type) values ('Green','Crank')
 insert @widget_table (widget_color, widget_type) values ('Blue','Cog')
 insert @widget_table (widget_color, widget_type) values ('Indigo','Flywheel')
 insert @widget_table (widget_color, widget_type) values ('Violet','Propeller')
  
 -- cursorless enumeration of the widget table 
 SET @i = 1
 WHILE (@i <= (SELECT MAX(id) FROM @widget_table))
    BEGIN
    -- get the widget color and type
    SELECT 
        @curr_widget = widget_color + ' ' + widget_type
    FROM 
        @widget_table WHERE id = @i
  
    PRINT @curr_widget
          
    -- increment counter for next row
    SET @i = @i + 1
 END

The key to making this work of course is to have an identity column to order the rows and the MAX function to know when you’re done. It’s a great little trick to try in your next project.