[sp_send_dbmail]將TABLE輸出成有隔行換色效果的HTML

        用Embed的CSS來實現比較簡便，但缺點不是所有的的收信應用程式都有支援，像Gmail就無法正常顯示，除非改用Inline的CSS囉

        像下圖這樣的隔行換色效果，看起來比較美觀，也比較專業

DECLARE @body VARCHAR(MAX)

--CSS

SET @body ='

<style type="text/CSS">

 table,th,td{

  width:70%;

  border-collapse:collapse;

  border: 1px solid #98bf21;

 }

 th{

  color:#FFFFFF;

  font-size:1.4em;

  background:#A7C942;

 }

 .A1{

  background:#DBFEBE;

 }

 .A2{

  background:#FFFFFF;

 }

 </style>

'

SET @body = @body + CAST((

   SELECT

      ( --設定欄名的地方

          SELECT

              'name'                AS 'th', NULL

             ,'database_id'         AS 'th', NULL

             ,'service_broker_guid' AS 'th', NULL

          FOR XML PATH('tr'), TYPE

      ),

      ( --資料表

          SELECT

             (CASE WHEN (a.cnt% 2 =0) THEN 'A1' ELSE 'A2' END) AS '@class'

             ,a.name               AS 'td', NULL

             ,a.database_id        AS 'td', NULL

             ,a.service_broker_guid AS 'td', NULL

          FROM ( SELECT   

                   name                               

                   ,database_id                      

                   ,service_broker_guid              

                   ,ROW_NUMBER() OVER ( ORDER BY name) 'cnt'

                FROM sys.databases

                WHERE database_id <5

              )a

          FOR XML PATH('tr'), TYPE

      )

   FOR XML PATH('table')

)AS VARCHAR(MAX))

--print @body

EXEC msdb.dbo.sp_send_dbmail

 @profile_name = 'default' ,

 @recipients = 'shaq.adalf@gmail.com',

 @subject = N'測試HTML郵件',

 @body = @body,

 @body_format = 'HTML';