title: Change the Colour of a value in a SQL Server DB Mail
body: <p>I have created a DB Mail alert that sends out a HTML formatted table showing the pass or fail status of my SQL agents.</p>
  
  <p>The alert works well, but the user has request an additional change where they want conditional formating on Pass/Fail status of the data, where failed status is to be highlighted RED and pass status to be highlighted GREEN.</p>
  
  <p>Is this possible to achieve to in SQL using DB Mail and HTML formatting? 
  A few forums have stated that JavaScript may need to be used but I'm not sure if its possible to even us JS in SQL.</p>
  
  <p>My code is below:</p>
  
  <pre><code>---------------------------------------------------------------------------- 
  ----------------------------------- Declare Variables ---------------------- 
  ---------------------------------------------------------------------------- 
  Declare @email_body nvarchar(max)
  Declare @email_profile_name nvarchar(max)
  Declare @email_recipients nvarchar(max)
  Declare @email_subject nvarchar(max)
  Declare @tableHTML nvarchar(MAX) = ''
  Declare @Style nvarchar(MAX) = ''
  Declare @Textdate nvarchar(max)
  DECLARE @crlf nvarchar = CHAR(13)+CHAR(10)
  Declare @Server nvarchar(max)
  
  
  ----------------------------------------------------------------------------            
  --------------------------- Set Varriables (That should not change) --------        
  ----------------------------------------------------------------------------    
  set @Server = @@SERVERNAME
  set @Textdate = cast(getdate() as date)
  Set @email_subject = @Textdate + ': Morning SQL Server Checks'
  
  ----------------------------------------------------------------------------    
  ----------------------------------- Set Varriables (User Input) ------------ 
  ---------------------------------------------------------------------------- 
  --Add other Email addresses as needed. Seperate with Semi-colon (;)
  set @email_recipients = 'Email@Email.com'
  
  
  ---------------------------------------------------------------------------- 
  ----------------------------------- Create Temp Tables --------------------- 
  ---------------------------------------------------------------------------- 
  IF OBJECT_ID('tempdb..#Results') IS NOT NULL 
  DROP TABLE #Results
  Create table #Results (
      [Server] nvarchar(max),
      [Entity] nvarchar(max),
      [Status] nvarchar(max),
      [Message] nvarchar(max),
      [Type] nvarchar(max))
  
  
  ----------------------------------------------------------------------------    
  ----------------------------------- Generate Data -------------------------- 
  ---------------------------------------------------------------------------- 
  --Gets SQL Agent Results (Script Derived from here: 
  https://social.msdn.microsoft.com/Forums/sqlserver/en-US/534cc5be-0021-4766-    
  9eef-92fea819e2e3/script-to-get-sql-server-agent-job-schedule-and-the-last- 
  run-status?forum=sqldatabaseengine)
  Insert into #Results
  SELECT @@SERVERNAME as 'Server Name'
        ,J.Name AS 'Job Name' 
        --,CASE J.Enabled  WHEN 1 THEN 'Yes' WHEN 0 THEN 'No' END as 'Job     
   Enabled'
        ,CASE WHEN LASTRUN.run_status = 0 THEN 'Failed'
              WHEN LASTRUN.run_status = 1 THEN 'Succeeded'
              WHEN LASTRUN.run_status = 2 THEN 'Retry'
              WHEN LASTRUN.run_status = 3 THEN 'Cancelled'
              ELSE 'Unknown' END as 'Last Run Status'                           
        ,LASTRUN.message as 'Last Run Message'
        ,'SQL Agent Jobs' as 'Type'
  FROM msdb.dbo.sysjobs J LEFT OUTER JOIN msdb.dbo.sysjobschedules JS ON     
  J.job_id = JS.job_id
                 LEFT OUTER JOIN msdb.dbo.sysschedules S ON JS.schedule_id = S.schedule_id
                 LEFT OUTER JOIN (SELECT J1.job_id
                                        ,J1.RUN_DURATION
                                        ,J1.run_date
                                        ,J1.run_time
                                        ,J1.message
                                        ,J1.run_status
                                   FROM msdb.dbo.sysjobhistory J1
                                   WHERE instance_id = (SELECT MAX(instance_id) 
                                                        FROM msdb.dbo.sysjobhistory J2 
                                                        WHERE J2.job_id = J1.job_id)) LASTRUN ON J.job_id = LASTRUN.job_id
  where J.Enabled = 1 --Only check for agents that have been enabled 
  -------------------------------------------------------------------------- 
  ----------------------------------- Generate Email ------------------------- 
  ---------------------------------------------------------------------------- 
  --Set Style (CSS)
  SET @Style += +N'&lt;style type="text/css"&gt;' + N'.tg  {border-    
   collapse:collapse;border-spacing:0;border-color:#aaa;}'
  + N'.tg td{font-family:Arial, sans-serif;font-size:14px;padding:10px 5px;border-style:solid;border-width:1px;overflow:hidden;word-break:normal;border-color:#aaa;color:#333;background-color:#fff;}'
  + N'.tg th{font-family:Arial, sans-serif;font-size:14px;font-weight:normal;padding:10px 5px;border-style:solid;border-width:1px;overflow:hidden;word-break:normal;border-color:#aaa;color:#fff;background-color:#f38630;}'
  + N'.tg .tg-9ajh{font-weight:bold;background-color:#68cbd0}' + N'.tg .tg-hgcj{font-weight:bold;text-align:center}'
  + N'&lt;/style&gt;';
  
   --Set Email Output (HTML Layout)
   Set @tableHTML += @Style + @tableHTML + N'&lt;H1 style="color:Blue;"&gt;' + 
   @Textdate + ': Morning SQL Server Checks&lt;/H1&gt;' +
                                      N'&lt;H2&gt;' + @Server + '&lt;/H2&gt;' +
                                      N'&lt;H3&gt;Databases&lt;/H3&gt;' +
  + N'&lt;table class="tg"&gt;' --DEFINE TABLE
  
  -- Define Headers for Database Check
  + N'&lt;tr&gt;'
  + N'&lt;H3&gt;SQL Agents&lt;/H3&gt;' +
  + N'&lt;table class="tg"&gt;' --DEFINE TABLE
  
  + N'&lt;tr&gt;'
  + N'&lt;td class="tg-9ajh"&gt;Server&lt;/td&gt;'
  + N'&lt;td class="tg-9ajh"&gt;Entity&lt;/td&gt;' 
  + N'&lt;td class="tg-9ajh"&gt;Status&lt;/td&gt;'
  + N'&lt;td class="tg-9ajh"&gt;Message&lt;/td&gt;'
  + N'&lt;td class="tg-9ajh"&gt;Type&lt;/td&gt;&lt;/tr&gt;'
  
  -- Define data for SQL Agent and cast to xml
  + Cast((
          Select td = isnull([Server],'###')
                ,''
                ,td = isnull([Entity],'###')
                ,''
                ,td = isnull([Status],'###')
                ,''
                ,td = isnull([Message],'###')
                ,''
                ,td = isnull([Type],'###')
                ,''
          from #Results
          where [type] = 'SQL Agent Jobs' FOR
           XML PATH('tr') ,
               TYPE
         ) AS NVARCHAR(MAX)) 
         + N'&lt;/table&gt;'; 
  
  -- Send the Email 
  EXEC msdb.dbo.sp_send_dbmail
       @profile_name = 'Outlook Support Profile', 
       @recipients = @email_recipients,
       @body = @tableHTML,
       @body_format = 'HTML',
       @subject = @email_subject 
  
  ----------------------------------------------------------------------------
  ----------------------------------- Cleanup --------------------------------
  ----------------------------------------------------------------------------
  drop table #Results
  </code></pre>
  
  <p>I hope the code is clear enough and makes sense.</p>
  
  <p>Thanks you
  Anthony</p>