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.