A post on the MSWebDev mailing list (archive only available to subscribers – but you should join, its a really useful list) asked ‘Does anyone think that set based is always possible and cursors need never be used?’.

I’m personally of the opinion that if cursors were never needed they wouldn’t be available to us, however there is a method I have used a number of times that works in more set based way, but still gives the ability to do per row processing. The main limitation is that the table(s)/views acting as the source must have a unique key. In the example below this key is a single field, but it should still be possible with composite key.

use tempdb
--Hide rows affected lines - they just clutter the messages window
Set NOCOUNT ON

--Create a table to store some data - this would be your complex table with many columns normally
create table Product (product_key bigint identity(1,1) not null, product_name nvarchar(200))

--Now fill the table with rather trival data
declare @count int
set @count = 0
while @count <1000
begin
insert into product (product_name) values ('Product' + cast (@count as nvarchar(5)))
set @count = @count +1
end
--uncomment below to see what that trivial data looks like
--select * from product

--This is the table that will act as our cursor
--     - using a table variable as performance is slightly better
--      as there is less logging than a temp table.  Also the scopeing works well for us
declare @keybag table (theKey bigint)
--Grab the keys of all the records for the 'cursor' - obviously this can be filtered and ordered
insert into @keybag select product_key from product

--the key of the record we are currently working on
declare @currentKey bigint

--variable for us to fill in a similar way to a cursor
declare @currentProduct nvarchar(200)

--loop for all keys in the @keyBag (NB we will be removing keys as we use them
while exists(select theKey from @keybag)
begin
--Get the next Key
select top 1 @currentKey = theKey from @keybag
-- and pull out the record from the real table using that key
-- note that more than one column can be pulled into a variable here too!
select @currentProduct = product_name from product where product_key = @currentKey
--Do per row processing - we just print the name out - but you can do whatever you like!
print  @currentProduct

--finally remove the key from the keybag as we have done it - forgetting this line will loop for ever :(
delete from @keybag where theKey = @currentKey
end

--clean up the test table
drop table product

I've done a little performance testing on this in the past to see how well it performs against cursors, but I don't have any numbers to hand - I'll have to check the Wiki at work to see if I put them there. I seem to revall that it was comparable or slightly faster in my tests, but I suspect your mileage will vary, but it can be a useful method.
Also worthy of note is c# Code Format the web based formatter I used to format the code above (even if it did mess up the comments when there are keywords in the line).

Update: Dropped the syntax highlighting when importing into this new blog

Comments Off on Cursing Cursors