Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 478
  • Last Modified:

How to build and send multiple tables using sp_send_dbmail

I have some sql that builds three separate tables based on different criteria and combines them into one email.

My problem is that if any one of the three conditions is not met, then none of the tables are built and a blank email is emailed. My code is below.

Is there a way to make the building of each HTML table conditional, so that if the condition is met, the code runs -- otherwise the code moves on to the next section?

Let me know if you have any ideas on how this can be done. I am at a loss.

FYI - the code shown below is the collective work of myself and two other EE Experts. I closed the question thinking that we were finished.

Thank you for your help,
Jason
DECLARE @tableHTML1 NVARCHAR(MAX) ;

SET @tableHTML1 = 
	N'<h2 style="font-family:verdana; font-size: 12px; padding: 0px; margin: 5px 0px 2px 0px;">Modified Employees</h2>' +
	N'<table style="font-family:verdana; font-size: 10px; border: 1px solid black; text-align: left; width: 100%">' +
	N'<tr style="background-color: Gray; color: white; text-align: left;">' +
	N'<th style="text-align: left; color: white;">Employee Number</th>' +
	N'<th style="text-align: left; color: white;">Full Name</th>' +
	N'<th style="text-align: left; color: white;">Password ID</th>' +
	N'<th style="text-align: left; color: white;">Job Code</th>' +
	N'<th style="text-align: left; color: white;">Effective Date</th>' +
	N'<th style="text-align: left; color: white;">Department Code</th>' +
	N'<th style="text-align: left; color: white;">Department</th>' +
	N'<th style="text-align: left; color: white;">Description</th>' +
	N'<th style="text-align: left; color: white;">Manager</th>' +
	N'</tr>' +
CAST ((	SELECT	td = [AssociateId], '',
				td = [Name], '',
				td = RIGHT([SSN],4), '',
				td = [JobCode], '',
				td = ISNULL(CONVERT(varchar,[EffDate],101), 'No Date Specified'), '',
				td = [DeptCode], '',
				td = [Title], '',
				td = [Description], '',
				td = [Manager], ''
		FROM [AssociateIndexTest].[dbo].[FSPI_AssociateIndexView]
		WHERE [Modified] = 1
		FOR XML PATH('tr'), TYPE
) AS NVARCHAR(MAX) ) +
	N'</table>'

DECLARE @tableHTML2 NVARCHAR(MAX) ;

SET @tableHTML2 = 
	N'<h2 style="font-family:verdana; font-size: 12px; padding: 0px; margin: 5px 0px 2px 0px;">New Employees</h2>' +
	N'<table style="font-family:verdana; font-size: 10px; border: 1px solid black; text-align: left; width: 100%">' +
	N'<tr style="background-color: Gray; color: white; text-align: left;">' +
	N'<th style="text-align: left; color: white;">Employee Number</th>' +
	N'<th style="text-align: left; color: white;">Full Name</th>' +
	N'<th style="text-align: left; color: white;">Password ID</th>' +
	N'<th style="text-align: left; color: white;">Job Code</th>' +
	N'<th style="text-align: left; color: white;">Start Date</th>' +
	N'<th style="text-align: left; color: white;">Department Code</th>' +
	N'<th style="text-align: left; color: white;">Department</th>' +
	N'<th style="text-align: left; color: white;">Description</th>' +
	N'<th style="text-align: left; color: white;">Manager</th>' +
	N'</tr>' +
CAST ((	SELECT	td = [AssociateId], '',
				td = [Name], '',
				td = RIGHT([SSN],4), '',
				td = [JobCode], '',
				td = ISNULL(CONVERT(varchar,[EffDate],101), 'No Date Specified'), '',
				td = [DeptCode], '',
				td = [Title], '',
				td = [Description], '',
				td = [Manager], ''
		FROM [AssociateIndexTest].[dbo].[FSPI_AssociateIndexView]
		WHERE [Modified] = 2
		FOR XML PATH('tr'), TYPE
) AS NVARCHAR(MAX) ) +
	N'</table>' ;

DECLARE @tableHTML3 NVARCHAR(MAX) ;

SET @tableHTML3 = 
	N'<h2 style="font-family:verdana; font-size: 12px; padding: 0px; margin: 5px 0px 2px 0px;">Terminated Employees</h2>' +
	N'<table style="font-family:verdana; font-size: 10px; border: 1px solid black; text-align: left; width: 100%">' +
	N'<tr style="background-color: Gray; color: white; text-align: left;">' +
	N'<th style="text-align: left; color: #FFFFFF;">Employee Number</th>' +
	N'<th style="text-align: left; color: #FFFFFF;">Full Name</th>' +
	N'<th style="text-align: left; color: #FFFFFF;">Password ID</th>' +
	N'<th style="text-align: left; color: #FFFFFF;">Job Code</th>' +
	N'<th style="text-align: left; color: #FFFFFF;">Termination Date</th>' +
	N'<th style="text-align: left; color: #FFFFFF;">Department Code</th>' +
	N'<th style="text-align: left; color: #FFFFFF;">Department</th>' +
	N'<th style="text-align: left; color: #FFFFFF;">Description</th>' +
	N'<th style="text-align: left; color: #FFFFFF;">Manager</th>' +
	N'</tr>' +
CAST ((	SELECT	td = [AssociateId], '',
				td = [Name], '',
				td = RIGHT([SSN],4), '',
				td = [JobCode], '',
				td = ISNULL(CONVERT(varchar,[EffDate],101), 'No Date Specified'), '',
				td = [DeptCode], '',
				td = [Title], '',
				td = [Description], '',
				td = [Manager], ''
		FROM [AssociateIndexTest].[dbo].[FSPI_AssociateIndexView]
		WHERE [Modified] = 1 AND [Status] = 'T'
		FOR XML PATH('tr'), TYPE
) AS NVARCHAR(MAX) ) +
	N'</table>' ;

DECLARE @tableHTML NVARCHAR(MAX)
SET @tableHTML = @tableHTML1 + @tableHTML2 + @tableHTML3
EXEC msdb.dbo.sp_send_dbmail
     @profile_name = 'WS0071_SQL_EMAIL',
     @recipients = 'me@domain.com',
	 @body = @tableHTML,
	 @body_format = 'HTML',
     @subject = 'These associates have been modified';

Open in new window

0
jsvb1977
Asked:
jsvb1977
  • 2
  • 2
1 Solution
 
Aneesh RetnakaranDatabase AdministratorCommented:
DECLARE @tableHTML NVARCHAR(MAX)
SET @tableHTML = CASE WHEN LEN(@tableHTML1) > 842 then @tableHTML1 ELSE '' END +
                 CASE WHEN LEN(@tableHTML2) > 833 THEN @tableHTML2 ELSE '' END +
                 CASE WHEN LEN(@tableHTML3) > 864 THEN @tableHTML3 ELSE '' END
IF @tableHTML <> ''
      EXEC msdb.dbo.sp_send_dbmail
             @profile_name = 'WS0071_SQL_EMAIL',
             @recipients = 'me@domain.com',
               @body = @tableHTML,
               @body_format = 'HTML',
             @subject = 'These associates have been modified';
0
 
jsvb1977Author Commented:
aneeshattingal,

That did the trick!

It seems like you are using the "length" of the html table [in characters, i think?] as the condition? If so, what is your logic here? where did you come up with the numbers 842, 833, 864?

Let me know your thoughts, please -- and thanks again for your assistance. It is greatly appreciated!

Jason

0
 
Aneesh RetnakaranDatabase AdministratorCommented:
as you said i am caluculating the length of each of the @tableHTMLs , for example this is how the length is calculated in case the first table returns nothing


DECLARE @tableHTML1 NVARCHAR(MAX) ;

SET @tableHTML1 =
      N'

Modified Employees

' +
      N'' +
      N'' +
      N'Employee Number' +
      N'Full Name' +
      N'Password ID' +
      N'Job Code' +
      N'Effective Date' +
      N'Department Code' +
      N'Department' +
      N'Description' +
      N'Manager' +
      N'' +
      N''

DECLARE @tableHTML2 NVARCHAR(MAX) ;
0
 
jsvb1977Author Commented:
i see. thank you for the explanation.
0

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now