CODEWITHSUNDEEP
sqlsql-serversql-server-2008sql-server-2005sql-server-2012
Muzz
Muzz

Reputation: 125

Sending email dynamically to multiple recipients with message using sp_send_dbmail

Actually i am looking to send Birthday greetings to the members. The recipients list i am getting dynamically. With the below query i can send the greetings to the members.

DECLARE @emails VARCHAR(500)
DECLARE @bodycontent VARCHAR(500)
SET @emails = '' 
SET @bodycontent = ''
use dnname
SELECT @emails = @emails + cm.PersonalEmail + ';' FROM tblIndividualMst  im
       inner join tblContactMst cm on cm.ContactID = im.ContactID
       where im.GroupID = 4673 and im.DateOfBirth = CONVERT(VARCHAR(10),GETDATE(),110)
DECLARE @recipList VARCHAR(500)
SET @recipList =  (select SUBSTRING(@emails, 0, LEN(@emails)))
SELECT @bodycontent = 'Happy BirthDay to'+ ' ' + + @bodycontent + 
       im.FullName + '' FROM tblIndividualMst  im
       inner join tblContactMst cm on cm.ContactID = im.ContactID
       where im.GroupID = 4673 and im.DateOfBirth = CONVERT(VARCHAR(10),GETDATE(),110)
use msdb
EXEC sp_send_dbmail @profile_name='eMail Profile',
    @recipients=@recipList,
    @subject='Happy Birthday',
    @body=@bodycontent, 
    @body_format = 'text'

So, if @emails have multiple recipients, how can i send the message to each members. 

DECLARE @emails VARCHAR(500)
DECLARE @bodycontent VARCHAR(500)
SET @emails = '' 
SET @bodycontent = ''
use dnname
SELECT @emails = @emails + cm.PersonalEmail + ';' FROM tblIndividualMst  im
       inner join tblContactMst cm on cm.ContactID = im.ContactID
       where im.GroupID = 4673 and im.DateOfBirth = CONVERT(VARCHAR(10),GETDATE(),110)
DECLARE @recipList VARCHAR(500)
SET @recipList =  (select SUBSTRING(@emails, 0, LEN(@emails)))
SELECT @Count = @Count + Count(cm.PersonalEmail) FROM tblIndividualMst  im
       inner join tblContactMst cm on cm.ContactID = im.ContactID
       where im.GroupID = 4673 and im.DateOfBirth = CONVERT(VARCHAR(10),GETDATE(),110)

Declare @i int
set @i = 0
while @i <= @Count 
  begin
SELECT @bodycontent = 'Happy BirthDay to'+ ' ' + + @bodycontent + 
       im.FullName + '' FROM tblIndividualMst  im
       inner join tblContactMst cm on cm.ContactID = im.ContactID
       where im.GroupID = 4673 and im.DateOfBirth = CONVERT(VARCHAR(10),GETDATE(),110)
use msdb
EXEC sp_send_dbmail @profile_name='eMail Profile',
   @recipients=@recipList,
   @subject='Happy Birthday',
   @body=@bodycontent,  
   @body_format = 'text'
end

So, how can i modify my query. Any help appreciated, thanks !!!

Upvotes: 1

Views: 11254

Answers (2)

S3S
S3S

Reputation: 25112

Here are some observations...

  1. You haven't declared @Count in your code anywhere. It would need to be declared as INT and set to 0
  2. In your WHILE LOOP you aren't incrementing @Count so this loop is infinite
  3. I would make @emails varchar(max) to avoid truncation of the recipient list
  4. @recipList is pointless as far as I can tell... you are setting it to the substring starting at 0 and going the entire length of all the emails... this is the same of saying set @recipList = @emails
  5. Your while loop isn't constructed logically with how you want this to work. It seems like your intent is to send a single email with all of the names who have a birthday. First, I would comma separate these names in @body. Secondly you don't need a loop for this at all. Remove the WHILE loop since all you need to do is send the email once, to the list of @recipList you have built, with the @bodycontent you are concatenating.

With that all being said... your code can be simplified to the below which should work.

use dnname

DECLARE @emails VARCHAR(max)
DECLARE @bodycontent VARCHAR(max)
DECLARE @people varchar(max) 
SET @emails = '' 
SET @people = ''
SET @bodycontent = ''



SELECT @emails = @emails + cm.PersonalEmail + ';' FROM tblIndividualMst  im
       inner join tblContactMst cm on cm.ContactID = im.ContactID
       where im.GroupID = 4673 and im.DateOfBirth = CONVERT(VARCHAR(10),GETDATE(),110)


SELECT @people = @people + im.FullName + ', ' 
       FROM tblIndividualMst  im
       inner join tblContactMst cm on cm.ContactID = im.ContactID
       where im.GroupID = 4673 and im.DateOfBirth = CONVERT(VARCHAR(10),GETDATE(),110)

SELECT @bodycontent = 'Happy BirthDay to '+ @people

EXEC msdb.dbo.sp_send_dbmail 
                           @profile_name='eMail Profile',
                           @recipients=@emails,
                           @subject='Happy Birthday',
                           @body=@bodycontent,  
                           @body_format = 'text'

IF you want to send one, personalized email, for each person... you can use a cursor

use dnname

DECLARE @emails VARCHAR(4000)
DECLARE @bodycontent VARCHAR(max)
DECLARE @people varchar(4000) 
SET @emails = '' 
SET @people = ''
SET @bodycontent = ''

DECLARE emailCursor CURSOR FOR

SELECT cm.PersonalEmail,im.FullName 
FROM tblIndividualMst  im
inner join tblContactMst cm on cm.ContactID = im.ContactID
where im.GroupID = 4673 and im.DateOfBirth = CONVERT(VARCHAR(10),GETDATE(),110)

OPEN emailCursor
FETCH NEXT FROM emailCursor INTO  @emails, @people

WHILE @@FETCH_STATUS = 0
BEGIN

SET @bodycontent = 'Happy BirthDay to '+ @people

EXEC msdb.dbo.sp_send_dbmail 
                           @profile_name='eMail Profile',
                           @recipients=@emails,
                           @subject='Happy Birthday',
                           @body=@bodycontent,  
                           @body_format = 'text'

FETCH NEXT FROM emailCursor INTO  @emails, @people



END
CLOSE emailCursor
DEALLOCATE emailCursor

Upvotes: 2

MWillemse
MWillemse

Reputation: 980

Personally I'm opposed to using loops in SQL and therefor try to avoid them as much as possible. The idea behind this is to perform as little statements as possible. In this case I'd generate a piece of dynamic SQL and execute that.

DECLARE @SQL NVARCHAR(MAX);
SELECT @SQL = CAST((
    SELECT [text()] = REPLACE(REPLACE('
        EXEC msdb.dbo.sp_send_dbmail 
            @profile_name=''eMail Profile'',
            @recipients=''{email}'',
            @subject=''Happy Birthday'',
            @body=''Happy BirthDay to {fullname}'',  
            @body_format = ''text'';
    '
    ,'{fullname}',im.FullName)
    ,'{email}',cm.PersonalEmail)
    FROM tblIndividualMst  im
        INNER JOIN tblContactMst cm 
            ON cm.ContactID = im.ContactID
    WHERE im.GroupID = 4673 
        AND im.DateOfBirth = CAST(GETDATE() AS DATE)
    FOR XML PATH('')
) AS NVARCHAR(max));
EXEC sp_executesql @SQL;

Let me explain what I'm doing here:

Declare @SQL and assign result the of the query casted to NVARCHAR(max).

DECLARE @SQL NVARCHAR(MAX);
SELECT @SQL = CAST((

XML engine is used to concat strings which is way faster than using normal contattenation, [text()] makes sure no XML tags will surround the SQL code.

    SELECT [text()] = REPLACE(REPLACE('

This is a template of the SQL code to be generated with placeholders that will be replaced.

        EXEC msdb.dbo.sp_send_dbmail 
            @profile_name=''eMail Profile'',
            @recipients=''{email}'',
            @subject=''Happy Birthday'',
            @body=''Happy BirthDay to {fullname}'',  
            @body_format = ''text'';
    '

Replacing placeholders ,'{fullname}',im.FullName) ,'{email}',cm.PersonalEmail) The query that will define how much iterations are needed.

    FROM tblIndividualMst  im
        INNER JOIN tblContactMst cm 
            ON cm.ContactID = im.ContactID
    WHERE im.GroupID = 4673 
        AND im.DateOfBirth = CAST(GETDATE() AS DATE)

Tell SQL to generate XML for this query but by suppying an empty string and using [text()] we've made sure no tags are actually included in the result.

    FOR XML PATH('')

Cast XML to NVARCHAR(max)

) AS NVARCHAR(max));

And finally execute!

EXEC sp_executesql @SQL;

Upvotes: 0

Related Questions