Cursorless Iteration in 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:
1: set nocount on;
2:
3: declare @i int
4: declare @curr_widget nvarchar(20)
5:
6: declare @widget_table TABLE (
7: id smallint primary key identity(1,1)
8: , widget_color nvarchar(10)
9: , widget_type nvarchar(10)
10: )
11:
12: -- populate the widget table with records
13: insert @widget_table (widget_color, widget_type) values ('Red','Widget')
14: insert @widget_table (widget_color, widget_type) values ('Orange','Gear')
15: insert @widget_table (widget_color, widget_type) values ('Yellow','Rotor')
16: insert @widget_table (widget_color, widget_type) values ('Green','Crank')
17: insert @widget_table (widget_color, widget_type) values ('Blue','Cog')
18: insert @widget_table (widget_color, widget_type) values ('Indigo','Flywheel')
19: insert @widget_table (widget_color, widget_type) values ('Violet','Propeller')
20:
21: -- cursorless enumeration of the widget table
22: SET @i = 1
23: WHILE (@i <= (SELECT MAX(id) FROM @widget_table))
24: BEGIN
25: -- get the widget color and type
26: SELECT
27: @curr_widget = widget_color + ' ' + widget_type
28: FROM
29: @widget_table WHERE id = @i
30:
31: PRINT @curr_widget
32:
33: -- increment counter for next row
34: SET @i = @i + 1
35: 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.
Looking for custom .NET solutions or help with an existing project? Give me a call at +1 (503) 475-3808 and let's talk about your situation.
My name is James Still and I'm a seasoned software developer living and working in Oregon USA. I'm an avid cyclist, backpacker, reader, stargazer, and I pick at the guitar from time to time. 
