October 28, 2009 at 9:35 am
Hello. I want to set up an automated email job that will email staff if they meet certain criteria. In this example I am using a happy birthday greeting scenario. How would I go about setting it up where each person would be emailed *individually* if their DOB was 10/11/78?
Here is some test data
DECLARE @MYTBL TABLE(NAME VARCHAR(10), EMAIL VARCHAR(50), DOB DATETIME)
INSERT @MYTBL
SELECT 'ADAM' [NAME], 'EMAIL1@YAHOO.COM' , '10/11/1978' [DOB] UNION ALL
SELECT 'TRACI' [NAME], 'EMAIL2@YAHOO.COM' , '10/11/1978' [DOB] UNION ALL
SELECT 'MAX' [NAME], 'EMAIL3@YAHOO.COM' , '1/1/1970' [DOB]
Obviously, Adam and Traci would be emailed. The key would be that each would receive an individual email.
Thanks in advance.
October 28, 2009 at 11:37 am
Create a temp table and fill it with the data you will need (names, emails, etc)
then create a loop to loop through that table and use sp_send_dbmail for each row.
Alternatively, you could also loop through a cursor, but that will yield poor performance compared to looping through a table variable or temp table.
October 28, 2009 at 11:43 am
Better answer no loops, we all know how much Jeff likes loops. <grin>
DECLARE @MYTBL TABLE(NAME VARCHAR(10), EMAIL VARCHAR(50), DOB DATETIME)
INSERT @MYTBL
SELECT 'ADAM' [NAME], 'EMAIL1@YAHOO.COM' , '10/11/1978' [DOB]
UNION ALL
SELECT 'TRACI' [NAME], 'EMAIL2@YAHOO.COM' , '10/11/1978' [DOB]
UNION ALL
SELECT 'MAX' [NAME], 'EMAIL3@YAHOO.COM' , '1/1/1970' [DOB]
DECLARE @CMD nvarchar(max)
SET @CMD = ''
SELECT @CMD = @CMD
+ 'EXEC msdb.dbo.sp_send_dbmail @recipients = '''
+
+ ''', @subject = ''Happy Birthday '
+ [NAME]
+ '!'', @body = ''Company A wants to wish you Happy Birthday on your special day!'', '
+ '@body_format = ''HTML''; '
+ CHAR(13) + CHAR(10)
FROM @MYTBL
WHERE DOB = '10/11/1978'
EXEC dbo.sp_executesql @stmt = @CMD
GO
I haven't tested this but it is a good basis either way.. The SELECT statement builds all the commands and then executes them..
CEWII
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply