DECLARE	@ProfileName		VARCHAR(64),
		@EmailsRecipients	VARCHAR(640),
		@EmailSubject		VARCHAR(640);

SET @ProfileName = 'Default Profile 01';
SET @EmailsRecipients = 'me@test.com';
SET @EmailSubject = 'Email Subject';

CREATE TABLE ##Results
(
	Col01				VARCHAR(12),
	Col02				VARCHAR(8)
);

INSERT INTO ##Results
SELECT
		Col01, Col02
FROM tbTable01

DECLARE @tableHTML NVARCHAR(MAX) ; 
	SET @tableHTML = 
	N'<html><body><h3>Title 01</h3>' + 
	N'<table border="1" cellspacing=0 cellpadding=5 width="55%">' + 
	N'<tr bgcolor="#3BB9FF">
		<td nowrap><b>Col01</b></td>
		<td nowrap><b>Col02   </b></td>
		</tr>' + 
			CAST(( 
		SELECT 
				'td/@bgcolor' = CASE WHEN Col01 <> 'Success' THEN 'yellow' ELSE '' END,
				td = FileDate, '',
				'td/@bgcolor' = CASE WHEN LEN(Col02) = 0 THEN 'yellow' ELSE '' END,
				td = FileTime, ''
		FROM ##Results 
		FOR XML PATH('tr'), TYPE) AS NVARCHAR(MAX)) + N'</table>
		</body></html>';

EXEC msdb.dbo.sp_send_dbmail 
	@recipients= @EmailsRecipients,
	@profile_name = @ProfileName,
	@subject = @EmailSubject,
	@body=@tableHTML,
	@body_format = 'HTML';

DROP TABLE ##Results;

Last modified: July 24, 2020

Author

Comments

Write a Reply or Comment