We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now

x

SQL email -- alternate HTML row colors

Medium Priority
1,827 Views
Last Modified: 2012-05-11
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
Comment
Watch Question

Unlock this solution with a free trial preview.
(No credit card required)
Get Preview
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a free trial preview!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.