Dec 12
TSQL, Looping through a result set without using database cursor
A few years ago a project that I was working on required me to loop through a result set from a select statement. Most examples that I saw used database cursors. However, I came across an old article that shows an alternative method for doing this. Let’s say we have a table that looks like this:
Table Name : Contacts
| ID | FirstName | LastName | EmailAddress |
| 1 | Jack | Bauer | jbauer@fox24.com |
| 2 | Dan | Marino | dmarino@dolphins.com |
| 3 | George | Bush | gbush@whitehouse.gov |
| 4 | Michael | Jordan | mjordan@bulls.com |
| 5 | Bill | Clinton | bj@whitehouse.gov |
Please don’t pay attention to the names. I’m sure you did so already. Anyway, let’s pretend that we had a scenario that we want to loop over the rows in this table and send an email out to these individuals. We can do this without using a database cursor. Here is some code to ponder on:
1: declare @email varchar(20)
2: declare @id int
3: declare @rowNum int
4: declare @maxrows int
5:
6: select top 1 @id = ID, @email = EmailAddress from Contacts
7: select @maxRows = count(*) from Contacts
8:
9: set @rowNum = 0
10:
11: – this will until the last row is reached
12: WHILE @rowNum < @maxRows
13: BEGIN
14: set @rowNum = @rowNum + 1
15: – this is where you can now do something like sending an email
16: – for now, we will just print the email to the output screen
17: print (‘Sending email to: ‘ + @email)
18:
19: – now we grab the next row making sure the ID of the next row
20: – is greater than previous row
21: select top 1 @id = ID, @email = EmailAddress from Contacts where ID > @id
22: END
I have tried to include additional comments in this code snippet so that it doesn’t require much explanation. However, you can see the original article if you need additional details.. Until next time…
Happy Coding!
December 12th, 2007 at 3:24 pm
Would you need to include ‘order by id’ in your two ’select top 1′ statements? I could be wrong, but I don’t think that SQL will order a result by id in every instance (although it typically does).
December 12th, 2007 at 3:50 pm
Sean,
Good catch. In most cases where your ID is the primary key, this will be included as an unique, non-clustered index so it would automatically sort ascending. However, I probably should have included an order by ID at the end of the select statements.
select top 1 @id = ID, @email = EmailAddress from Contacts order by ID
select top 1 @id = ID, @email = EmailAddress from Contacts where ID > @id order by ID
Thanks.
December 12th, 2007 at 4:38 pm
I haven’t seen any comparisons, but I still think using a Cursor will be more performant on large datasets since there are just so many small queries being done with this.
December 12th, 2007 at 6:36 pm
Yeah, I see your point. But hey, you can always pause the execution by using the WAITFOR DELAY statement. For example after the PRINT() statement you can insert WAITFOR DELAY ‘00:00:10′ which could pause the execution of the while loop for 10 seconds.
Thanks for the feedback.