Back to Home

Back to Computer and Internet

Buy e-books

Converting Cursors to a Set Based Solution - Part I
 by: Nicholas Edwards

The biggest hurdle a developer has to overcome when moving from traditional 3rd generation languages to T_SQL is moving the mindset from CURSOR based solutions to SET based solutions.

Don't get me wrong, I believe CURSOR based solutions have their place, and when working with small datasets you won't notice much of a difference in speed. It is only when working with the lart datasets (100,000+ rows) you will notice a dramatic improvement in speed.

I believe there is nothing like a few good examples so I will endeavour to provide multiple examples of the next few posts.

Simple Example 1:

We have two tables one contains a subset of the other; we need to move all the data from the main table to the subset table.

Customer
--------
ID
Name
StreetAddress
Suburb
City
ZIP
Sex
MartialStatus

MailingList
-----------
Name
Street
Suburb
City
ZIP

The CURSOR based solution to do this is:

DECLARE @Name varchar(50), @Street varchar(50)
DECLARE @Suburb varchar(50), @City varchar(50)
DECLARE @ZIP varchar(50)

DECLARE CustomerCursor CURSOR FOR
  SELECT Name, StreetAddress, Suburb, City, ZIP
    FROM Customer
        
OPEN CustomerCursor
        
FETCH NEXT FROM CustomerCursor INTO @Name, @Street, @Suburb, @City, @ZIP

WHILE @@FETCH_STATUS = 0
BEGIN
  INSERT INTO MailingList
    (Name, Street, Suburb, City, ZIP)
  VALUES
    (@Name, @Street, @Suburb, @City, @ZIP)

  FETCH NEXT FROM CustomerCursor INTO @Name, @Street, @Suburb, @City, @ZIP
END

CLOSE CustomerCursor
DEALLOCATE CustomerCursor

The SET based solution to do this is:

INSERT INTO MailingList
  SELECT Name, StreetAddress, Suburb, City, ZIP
    FROM Customer

As you can probably see from the above example the simple SET based solution is much more efficient and easier to read. The SET based solution also has one other advantage, if the field sizes are to change then you don't need to worry about the code.

About The Author Nicholas Edwards is co-owner of http://www.ersysgroup.com, a web site about our experiences with starting an online empire. Nicholas has also dedicated himself to sharing technical know-how.

This article was posted on December 28, 2005