将查询导出到html表时，浮点值的格式不正确
将查询导出到html表时，浮点值的格式不正确
提问于 2017-07-11 17:59:48
我需要一些帮助。我使用这段代码将SQL查询导出到html表(从Convert a SQL query result table to an HTML table for email获得代码)。
-- Description: Turns a query into a formatted HTML table. Useful for emails.
-- Any ORDER BY clause needs to be passed in the separate ORDER BY parameter.
-- =============================================
CREATE PROC [dbo].[spQueryToHtmlTable]
(
@query nvarchar(MAX), --A query to turn into HTML format. It should not include an ORDER BY clause.
@orderBy nvarchar(MAX) = NULL, --An optional ORDER BY clause. It should contain the words 'ORDER BY'.
@html nvarchar(MAX) = NULL OUTPUT --The HTML output of the procedure.
)
AS
BEGIN
SET NOCOUNT ON;
IF @orderBy IS NULL BEGIN
SET @orderBy = ''
END
SET @orderBy = REPLACE(@orderBy, '''', '''''');
DECLARE @realQuery nvarchar(MAX) = '
DECLARE @headerRow nvarchar(MAX);
DECLARE @cols nvarchar(MAX);
SELECT * INTO #dynSql FROM (' + @query + ') sub;
SELECT @cols = COALESCE(@cols + '', '''''''', '', '''') + ''['' + name + ''] AS ''''td''''''
FROM tempdb.sys.columns
WHERE object_id = object_id(''tempdb..#dynSql'')
ORDER BY column_id;
SET @cols = ''SET @html = CAST(( SELECT '' + @cols + '' FROM #dynSql ' + @orderBy + ' FOR XML PATH(''''tr''''), ELEMENTS XSINIL) AS nvarchar(max))''
EXEC sys.sp_executesql @cols, N''@html nvarchar(MAX) OUTPUT'', @html=@html OUTPUT
SELECT @headerRow = COALESCE(@headerRow + '''', '''') + ''<th>'' + name + ''</th>''
FROM tempdb.sys.columns
WHERE object_id = object_id(''tempdb..#dynSql'')
ORDER BY column_id;
SET @headerRow = ''<tr>'' + @headerRow + ''</tr>'';
SET @html = ''<table border="1">'' + @headerRow + @html + ''</table>'';
';
EXEC sys.sp_executesql @realQuery, N'@html nvarchar(MAX) OUTPUT', @html=@html OUTPUT
END
GO这段代码运行得很完美，但在格式化浮点值时有一个问题。例如：
SELECT Name, Weight FROM Products从Management Studio执行该查询时，将返回类似以下内容：
Name1 | 1073,822
Name2 | 179,554当我使用存储过程将其导出到html表时，我会得到如下结果：
Name1 | 1.073822000000000e+003
Name2 | 1.795540000000000e+002不知道如何更改存储过程以适应它，从而避免浮点值上的这种错误格式。
在这方面的任何帮助都将不胜感激。
回答 2
Stack Overflow用户
回答已采纳
发布于 2017-07-11 19:40:52
您正在使用cast显示html-table中的值：
SET @cols = ''SET @html = CAST(( SELECT '' + @cols + '' FROM #dynSql ' + @orderBy + ' FOR XML PATH(''''tr''''), ELEMENTS XSINIL) AS nvarchar(max))'' MS-Help告诉您更多，但基本上这是使用浮点值进行强制转换的自动行为。
在传递的查询中，您应该尝试使用STR()函数将浮点型字段转换为所需的格式，请参见str function description
Stack Overflow用户
发布于 2017-07-11 20:43:57
非常感谢您的回复
编辑:如果我去掉对varchar(max)的强制转换，结果似乎是一样的。
但我只是试着这样做：
SELECT Name, CAST(Weight AS varchar(max)) FROM Products而且我仍然可以看到正确的格式化的值：
Name1 | 1073.82
Name2 | 179.554否则，我将使用此SP从许多站点和进程调用。不可能检查每个大小写并尝试强制转换浮点型字段。我需要一个在SP级别实施的解决方案。我尝试使用STR()函数，因此更改了行：
SET @cols = ''SET @html = CAST(( SELECT '' + @cols + '' FROM #dynSql ' + @orderBy + ' FOR XML PATH(''''tr''''), ELEMENTS XSINIL) AS nvarchar(max))''至
SET @cols = ''SET @html = STR(( SELECT '' + @cols + '' FROM #dynSql ' + @orderBy + ' FOR XML PATH(''''tr''''), ELEMENTS XSINIL))''但随后我得到了错误:将数据类型nvarchar转换为float时出错。不知道这是不是你提到的。
是否可以检查SP中的字段类型，并对浮动情况使用不同的强制转换？
我怎么能做到呢？
致以敬意，
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/45031295
复制相关文章
相似问题