CODEWITHSUNDEEP
sql-serversp-send-dbmail
LegalEagle
LegalEagle

Reputation: 117

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 = '[email protected]'
, @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!!

Upvotes: 2

Views: 5090

Answers (3)

Arulkumar
Arulkumar

Reputation: 13247

Instead of td = '$'+ROUND(current1,2), '', this line, please use the below line it will solve your problem.

td = CONCAT('$', ROUND(current1, 2)), '',

Sample execution with sys.objects table with @current1 as Money data type.

DECLARE @BodyText NVARCHAR(MAX);
DECLARE @current1 AS Money = '50.000';

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 = [type_desc],    '',
                td = Name,  '',
                td = CONCAT('$', ROUND(@current1, 2)), '',
                td = [type],  ''
          FROM SYS.objects 
          WHERE [type] = 'U' 
          FOR XML PATH('tr'), TYPE 
) AS NVARCHAR(MAX) ) +
N'</table>' ;

--PRINT @BodyText

Upvotes: 1

EoinS
EoinS

Reputation: 5482

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

Upvotes: 0

Krishnraj Rana
Krishnraj Rana

Reputation: 6656

With SQL Server 2012 and higher version, you can use FORMAT function to get value with currency symbol. In your case its like this

SELECT 
    ...
    td = FORMAT(current1, 'C', 'en-us')
FROM
    ...

and for SQL Server 2008 you can achieve it like this -

SELECT 
    ...
    td = '$'+CAST(CAST(current1 AS DECIMAL(10, 2)) AS VARCHAR)
FROM
    ...

Upvotes: 1

Related Questions