Use send_dbmail to send an email for each row in sql table

Question

I am having difficulties figuring out how to make send_dbmail loop through my table and send a new email for each record. My table is dropped and re-created each time this package runs, sometimes it will have zero records, other times it may have a few (never anything significant, always < 10).

This works fine when I have a single row but when I add a second row the subquery fails as it doesn't produce a unique result, so my question is how can I make this send a separate email for each row that might exists and how do I make it not send a message if no rows exists?

I have to send this via a TEXT body format as it is being grabbed by a third party mail eater that only supports text.

Thanks for any suggestions!

use automationdb
declare @bodytext as nvarchar(max)

set @bodyText = ( SELECT 
N'%z_Curr_Contact_Number = 
%string1 = na
%string1 = na
%string2 = na
%zneed_by_date = '+ CONVERT(VARCHAR(20), dateadd(day,1,GETDATE()),100) + N'
' + '%Description=' + 'ID:  ' + isnull(id,'Unknown') +  N'
' + '%Description=' + 'Name:  ' + isnull(name,'Unknown') + N'
' + '%Description=' + 'Login:  ' + isnull(login,'Unknown') + N'
' + '%Description=' + 'Status:  ' + isnull(status,'Unknown') + N'
' + '%Description=' + 'Note One:  ' + isnull(note_one,'Unknown') + N'
' + '%Description=' + 'Role:  ' + isnull(role_id,'Unknown') + N'
' + '%Description=' + 'Role Name:  ' + isnull(role_name,'Unknown') + N'
' + '%Description=' + 'Location:  ' + isnull(Location,'Unknown') + N''

     FROM dbo.mpt)


EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'test',
@body = @bodyText,
@body_format ='TEXT',
@recipients = 'random@email.com',
@subject = 'send_dbmail test' ;

Bogdan Sahlean · Accepted Answer · 2013-11-30T22:26:30.827

1) You could use a LOCAL FAST_FORWARD cursor to read every row and then to execute sp_send_dbmail

or

2) You could dynamically generate a sql statement that includes the list of EXEC sp_send_dbmail statements like this:

DECLARE @SqlStatement NVARCHAR(MAX) = N'
    EXEC msdb.dbo.sp_send_dbmail @recipients=''dest01@domain.com'', ...; 
    EXEC msdb.dbo.sp_send_dbmail @recipients=''dest02@domain.com'', ...; 
    EXEC msdb.dbo.sp_send_dbmail @recipients=''dest03@domain.com'', ...;
    ...';
EXEC(@SqlStatement);

or

DECLARE @bodyText NVARCHAR(MAX);
SET @bodyText = ...;

DECLARE @SqlStatement NVARCHAR(MAX) = N'
    EXEC msdb.dbo.sp_send_dbmail @recipients=''dest01@domain.com'', @body = @pBody, ...; 
    EXEC msdb.dbo.sp_send_dbmail @recipients=''dest02@domain.com'', @body = @pBody, ...; 
    EXEC msdb.dbo.sp_send_dbmail @recipients=''dest03@domain.com'', @body = @pBody, ...; 
    ...';
EXEC sp_executesql @SqlStatement, N'@pBody NVARCHAR(MAX)', @pBody = @bodyText;

Thanks, the later of the above is a nice solution to avoid using a cursor. How could one make the number of Exc in the @SQLStatement changeable with the number of rows in the table, i.e. if there were 4 lines in the table only 4 EXEC are in the dynamic SQL? Is this possible? — , Oct 25 '19 at 14:24

Use send_dbmail to send an email for each row in sql table

1 Answers1