Obtaining a bad format on floating-point values ​​when exporting the query to the html table

advertisements

I need some help on this. I use this code to export sql queres to html tables (got code from 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'', @[email protected] 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', @[email protected] OUTPUT
END
GO

The code works perfect, but has one problem formatting float values. For example:

SELECT Name, Weight FROM Products

The query returns something like this when executed from Management Studio:

Name1 | 1073,822
Name2 | 179,554

When I use the stored procedure to export this to html table, then I get the results like this:

Name1 | 1.073822000000000e+003
Name2 | 1.795540000000000e+002

Don't know exactly how to change the stored procedure to adapt it in order to avoid this wrong formatting on float values.

Any help on this would be appreciated.


you are using cast to show the values in the html-table:

SET @cols = ''SET @html = CAST(( SELECT '' + @cols + '' FROM #dynSql ' + @orderBy + ' FOR XML PATH(''''tr''''), ELEMENTS XSINIL) AS nvarchar(max))''

MS-Help tells you more, but basically this is the automatic behavior of cast with a float value.

You should try to already convert your float-fields to the desired format in the query you pass, using the STR() function, see str function description