Formatting query data in HTML email from SQL Server 2008

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:

DECLARE @BodyText NVARCHAR(MAX);

SET @BodyText =
N'Please notify the attorney of the direct pay(s) shown below:<BR><BR>' +
N'<table border="1">' +
N'<tr><th>File</th><th>Name</th><th>Balance</th><th>Atty File</th>' +
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'</table>' ;


--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';

The issue is with the "current1" field from the master table. Even with the code above, that field still display like "50.000".

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?

Thanks in advance!!

Solution

You have the right idea but there are two caveats: value rounding vs formatting and String+Float problems .

Round() accepts a numeric expression and uses the length parameter to determine the precision to which numeric_expression is to be rounded.

The value is rounded, but the format is not.

For example:

ROUND(current1 , -1) = 50.000

Your value has 3 decimal places. If you wish to reflect a different number of decimals you have to cast your value to be a decimal with that length ie

CAST(current1 AS DECIMAL(10, 2)) = 50.00

Now is where the concatenation of strings comes in. This value is still a float and you cannot combine with a string. This is where you need to cast as Varchar AND concatenate with '$'

'$'+CAST(CAST(current1 AS DECIMAL(10, 2)) AS VARCHAR) = $50.00

This solution is for Sql Server 2008.

Links:

SQL Fiddle examples

TOTN : ROUND

SO : Concat String and Float