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
saturationAsked:
Who is Participating?
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.