[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

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

SQL email -- alternate HTML row colors

My stored proc below creates an HTML email that feeds the recordset from the view into an XML-PATH (I found the example on the MS site).   How can I alternate the row color doing it this way?


USE [MYDB]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[AlertsOpportunities]


AS
BEGIN


DECLARE @tableHTML  NVARCHAR(MAX) ;

SET @tableHTML =
    N'<style>body{font-family:arial;font-size:11px;}h2 {font-family:arial;font-size:15px;}td{vertical-align:text-top;text-align:left;}</style>' +
    N'<table cellpadding=0 cellspacing=2 width=100%>' +
    N'<tr><th align=left valign=top>Status</th><th align=left valign=top>Stage</th>' +
    N'<th align=left valign=top>Created</th><th align=left valign=top>Updated</th>' +
    N'<th align=left valign=top>Description</th><th align=left valign=top>Company</th>' +
    N'<th align=left valign=top>Person</th><th align=left valign=top>Email</th><th align=left valign=top>Assigned</th></tr>' +
    CAST ( ( SELECT td = Status, '',
                    td = Stage, '',
                    td = Created, '',
                    td = Updated, '',
                    td = Description, '',
                    td = Company, '',
                    td = Person, '',
                    td = Email, '',
                    td = Assigned, ''
              FROM  MYDB.dbo.vAlertsOpportunities ORDER BY Status
              FOR XML PATH('tr'), TYPE
    ) AS NVARCHAR(MAX) ) +
    N'</table>' ;

EXEC msdb.dbo.sp_send_dbmail @recipients='myemail@test.com',
    @subject = 'Daily Update: Current Opportunities',
    @body = @tableHTML,
    @body_format = 'HTML',
    @profile_name = 'myaccount';


END
0
saturation
Asked:
saturation
1 Solution

Featured Post

NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

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