从SQL Server 2008格式化HTML电子邮件中的查询数据

Question

我正试图让SQL Server 2008发送HTML格式的电子邮件，但我在查询中提取的字段之一是"money"数据类型，因此在小数点后显示3位数字，我似乎无法显示美元符号。以下是我目前所拥有的：

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 = '[e-mail]'
, @subject = 'test html email'
, @Body = @BodyText
, @body_format = 'HTML';

问题在于主表中的"current1"字段。即使有上面的代码，该字段仍然显示为"50.000"

如果我必须将Cast作为NVarchar才能使用动态SQL，我如何使该字段在最终电子邮件中显示为"$50.00"？

提前感谢！！

Answer 1

使用SQL Server 2012及更高版本，可以使用FORMAT函数获取带有货币符号的值。在你的情况下，它就像这个

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

对于SQL Server 2008，您可以像这样实现

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

Answer 2

请使用下面的行，而不是td = '$'+ROUND(current1,2), '',这一行，它将解决您的问题。

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

使用sys.objects表执行示例，@current1为Money数据类型。

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

Answer 3

您的想法是正确的，但有两个注意事项：值舍入与格式设置以及字符串+浮点问题。

Round（）接受一个数值表达式，并使用length参数来确定numeric_expression要舍入到的精度。

值是四舍五入的，但格式不是。

例如：

ROUND(current1 , -1) = 50.000

您的值有3位小数。如果你想反映不同数量的小数，你必须将你的值转换为具有该长度的小数，即

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

现在是字符串串联的地方。这个值仍然是一个浮点值，不能与字符串组合。这是您需要转换为Varchar并与"$"连接的地方

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

此解决方案适用于Sql Server 2008。

链接：

SQL Fiddle示例

TOTN：圆形

SO：凹字符串和浮点