Getting bad format on float values when exporting query to html table

Question

I need some help on this. I use this code to export SQL queries 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'', @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

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.

Answer 1

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

Answer 2

Many thanks for your reply

EDIT: If I take out the casting to varchar(max), the result seems to be the same.

But I've just tried to do something like this:

SELECT Name, CAST(Weight AS varchar(max)) FROM Products

And I still can see the right formatted values:

Name1 | 1073.82
Name2 | 179.554

Otherwise, I use this SP to be called from many sites and processes. Is not possible to check every case and try to cast the float fields. I need a solution to be implemented at the SP level. I've tried to use the STR() function, so changed the line:

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

To

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

But then I'm getting the error: Error converting data type nvarchar to float. Not sure if this is what you mentioned.

Would be a way to check the field type in the SP, and use a different casting for the float cases?

How could I do it?

Regards,