SQL email -- alternate HTML row colors

Posted on 2011-04-20
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?



CREATE PROCEDURE [dbo].[AlertsOpportunities]



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='',
    @subject = 'Daily Update: Current Opportunities',
    @body = @tableHTML,
    @body_format = 'HTML',
    @profile_name = 'myaccount';

Question by:saturation
    1 Comment
    LVL 9

    Accepted Solution


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Why You Should Analyze Threat Actor TTPs

    After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

    Suggested Solutions

    If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
    In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
    In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
    Here's a very brief overview of the methods PRTG Network Monitor ( offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…

    759 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    12 Experts available now in Live!

    Get 1:1 Help Now