Email Formatted HTML Table with T-SQL

  • tgqthi

    SSC Journeyman

    Points: 79

    Jeff Moden (3/15/2016)


    tauseef.jan (3/15/2016)


    Thankyou so much for the code snippet.

    But I have a problem, that I have EMPTY(NULL) columns.

    because of this the columns in the email get shifted to the left.

    Please help to resolve this

    Most likely, you'll need to replace the "empty" cells with non-breaking spaces (I forget the HTML entitization code for that but should be easy for you to find).

    use ISNULL

    Example:

    SET @BODY = ISNULL(@Sintetico,'') + @QuebraN + ISNULL(@ExplodeRJ,'')

    EXEC msdb.dbo.sp_send_dbmail

    @recipients='ti@thermoklima.com.br',

    @subject = '',

    @BODY = @BODY,

    @body_format = 'HTML'

  • Jeff Moden

    SSC Guru

    Points: 996807

    tgqthi (8/30/2016)


    Jeff Moden (3/15/2016)


    tauseef.jan (3/15/2016)


    Thankyou so much for the code snippet.

    But I have a problem, that I have EMPTY(NULL) columns.

    because of this the columns in the email get shifted to the left.

    Please help to resolve this

    Most likely, you'll need to replace the "empty" cells with non-breaking spaces (I forget the HTML entitization code for that but should be easy for you to find).

    use ISNULL

    Example:

    SET @BODY = ISNULL(@Sintetico,'') + @QuebraN + ISNULL(@ExplodeRJ,'')

    EXEC msdb.dbo.sp_send_dbmail

    @recipients='ti@thermoklima.com.br',

    @subject = '',

    @BODY = @BODY,

    @body_format = 'HTML'

    Pretty sure that won't actually help with empty "Cells" because HTML basically ignores leading/trailing spaces and cells that contain nothing but spaces.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    "If "pre-optimization" is the root of all evil, then what does the resulting no optimization lead to?"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • welcome.to.route66

    SSC Veteran

    Points: 249

    take a look at vsql-email (sql-email.com) this tool is exactly what I needed

  • DamianC

    SSCertifiable

    Points: 7831

    I tend to drop whatever I need to audit from the ETL process into a table, the use a BI tool (in my case, SSRS or Cognos) to produce and send a report

    That said, I was aware but had never really played around with e-mailing with formatting

    Just used the ideas presented here (initial plus some of the discussed alternatives) to produce a useful report that runs part way through my ETL build

    Thanks

    - Damian

  • thakur.bhagat24

    SSC Rookie

    Points: 45

    In previous example is very useful

    But I have one new requirement of order by color what we get using below case.

    CASE WHEN (ROW_NUMBER() OVER (ORDER BY [SpecialOfferID]))%2 =1 THEN '#A3E0FF' ELSE '#8ED1FB' END

    i.e order by '#A3E0FF' OR

    i.e Order by '#8ED1FB'

  • Lowell

    SSC Guru

    Points: 323456

    thakur.bhagat24 (12/20/2016)


    In previous example is very useful

    But I have one new requirement of order by color what we get using below case.

    CASE WHEN (ROW_NUMBER() OVER (ORDER BY [SpecialOfferID]))%2 =1 THEN '#A3E0FF' ELSE '#8ED1FB' END

    i.e order by '#A3E0FF' OR

    i.e Order by '#8ED1FB'

    you can use Cascading Style Sheets and the nth-child to set the row color of every other row automatically:

    the

    <style TYPE="text/css">

    tr:nth-child(odd) { background-color:#eee; }

    tr:nth-child(even) { background-color:#fff; }

    </STYLE>

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • This was removed by the editor as SPAM

  • cunay

    Grasshopper

    Points: 22

    nakache - Thursday, June 20, 2013 1:35 AM

    Why not using CSS?Example: DECLARE @bodyMsg nvarchar(max) DECLARE @subject nvarchar(max) DECLARE @tableHTML nvarchar(max) SET @subject = 'Query Results in HTML with CSS' SET @tableHTML = N'<style type="text/css"> #box-table { font-family: "Lucida Sans Unicode", "Lucida Grande", Sans-Serif; font-size: 12px; text-align: center; border-collapse: collapse; border-top: 7px solid #9baff1; border-bottom: 7px solid #9baff1; } #box-table th { font-size: 13px; font-weight: normal; background: #b9c9fe; border-right: 2px solid #9baff1; border-left: 2px solid #9baff1; border-bottom: 2px solid #9baff1; color: #039; } #box-table td { border-right: 1px solid #aabcfe; border-left: 1px solid #aabcfe; border-bottom: 1px solid #aabcfe; color: #669; } tr:nth-child(odd) { background-color:#eee; } tr:nth-child(even) { background-color:#fff; } </style>'+ N'<H3><font color="Red">All Rows From [AdventureWorks].[Sales].[SpecialOffer]</H3>' + N'<table id="box-table" >' + N'<tr><font color="Green"><th>SpecialOfferID</th> <th>Description</th> <th>Type</th> <th>Category</th> <th>StartDate</th> <th>EndDate</th> </tr>' + CAST ( ( SELECT td = CAST([SpecialOfferID] AS VARCHAR(100)),'', td = [Description],'', td = [Type],'', td = [Category] ,'', td = CONVERT(VARCHAR(30),[StartDate],120) ,'', td = CONVERT(VARCHAR(30),[EndDate],120) FROM [AdventureWorks].[Sales].[SpecialOffer] ORDER BY [SpecialOfferID] FOR XML PATH('tr'), TYPE ) AS NVARCHAR(MAX) ) + N'</table>' EXEC msdb.dbo.sp_send_dbmail @recipients='AnyMailYouWant@SqlIsCool.com', @subject = @subject, @body = @tableHTML, @body_format = 'HTML' ;Result:

    nakache, thanks for your help, I did not know SQL supports for CSS. I was editing all the HMTL tags from the beginning for each code. This saved my time bro.

Viewing 8 posts - 76 through 83 (of 83 total)

You must be logged in to reply to this topic. Login to reply