title: Formatting query data in HTML email from SQL Server 2008
body: <p>I am trying to get SQL Server 2008 to send HTML-formatted email, however one of the fields I am pulling in my query is a "money" data type and therefore displays with 3 digits after the decimal place and I can't seem to get the dollar sign to show up. Here is what I have so far:</p>
  
  <pre><code>DECLARE @BodyText NVARCHAR(MAX);
  
  SET @BodyText =
  N'Please notify the attorney of the direct pay(s) shown below:&lt;BR&gt;&lt;BR&gt;' +
  N'&lt;table border="1"&gt;' +
  N'&lt;tr&gt;&lt;th&gt;File&lt;/th&gt;&lt;th&gt;Name&lt;/th&gt;&lt;th&gt;Balance&lt;/th&gt;&lt;th&gt;Atty File&lt;/th&gt;' +
  CAST ( ( SELECT td = number,    '',
                  td = Name,  '',
                  td = '$'+ROUND(current1,2), '',
                  td = CC.AttorneyAccountID,  ''
  from master 
      inner join CourtCases CC on master.number = CC.AccountID
  where number = 1234567
            FOR XML PATH('tr'), TYPE 
  ) AS NVARCHAR(MAX) ) +
  N'&lt;/table&gt;' ;
  
  
  --Notify legal team of legal DPs
  exec msdb.dbo.sp_send_dbmail 
  @profile_name = 'Default'
  , @recipients = 'me@mycompany.com'
  , @subject = 'test html email'
  , @Body = @BodyText
  , @body_format = 'HTML';
  </code></pre>
  
  <p>The issue is with the "current1" field from the master table.  Even with the code above, that field still display like "50.000". </p>
  
  <p>How can I make that field show up as "$50.00" in the final email if I have to have the Cast as NVarchar in order to use the dynamic SQL?  </p>
  
  <p>Thanks in advance!!</p>