Looping within a Stored Procedure

[BoydMT]

As a VBA programmer, I've been creating loops within my code for years. However, I now want to do the same using SQL Server, but have heard to avoid them if at all possible. I have a table named 'tblFollowUp'. The table consists of 3 fields ([Name], [Date], [Comment]). An example of the data in this table is as follows:
John 01/01/2004 JohnsComment1
John 01/15/2004 JohnsComment2
Tom 04/20/2004 TomsComment1
Mary 02/15/2004 MarysComment1
Mary 04/15/2004 MarysComment2
Mary 05/01/2004 MarysComment3

Basically, I need to grab the unique names from the table (John, Tom, and Mary), then send each person an email containing their comments. I will be using xp_sendmail for the actual email process.

In VBA, I would code it something like this:

Set rst1 = db.OpenRecordset("SELECT Name FROM tblFollowUp GROUP BY Name")
Do Until rst1.EOF = True
   Set rst2 = db.OpenRecordset("SELECT * FROM tblFollowUp WHERE Name = '" & rst1.Name & "'")
   Export records to file ...
   Send email using xp_sendmail ...
   rst1.MoveNext
Loop

I am new to creating stored procedures, and am curious if anyone has done this type of thing before?

Thanks,
Boyd

 

[SQLSister]

OK I don't see anywhere that includes the eamil with the name. In fact the first question is how do you determine a unique name? There could be multiple John Smith's in your database.

If you want to send one message with the same information to everyone and not worry about them seeing each other's email addresses, you do not need a loop. If you want to run the processs so it sends each person an email addressed to that person only, then yes you will need to loop.

There are two ways to loop - using the While keyword or using a cursor. You can look them up in Books Online to get the syntax.





Questions about posting. See faq183-874