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

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!