Changing money to numeric breaks this dynamic SQL approach-CodePudding

This article has a "query to html" approach which seems clean and elegant. But if I change the money data type to numeric, I get an error:

converting data type varchar to numeric

My version:

CREATE OR ALTER PROC dbo.usp_ConvertQuery2HTMLTable 
AS
BEGIN
   DECLARE @columnslist NVARCHAR (1000) = ''
   DECLARE @restOfQuery NVARCHAR (2000) = ''
   DECLARE @DynTSQL NVARCHAR (3000)
   DECLARE @FROMPOS INT

   SET NOCOUNT ON

CREATE TABLE #Products
(
   Product [varchar](50) NULL,
   UnitPrice [numeric](20, 6) NULL
)


INSERT INTO #Products
SELECT 'Nougat Creme', 14.00
UNION
SELECT 'Gorgonzola', 12.00
UNION
SELECT 'Ale', 14.00
UNION
SELECT 'Chocolate', 10.00
UNION
SELECT 'Lager', 12.00
UNION
SELECT 'Bread', 9.00

   DECLARE @SQLQuery NVARCHAR(3000) = 'SELECT Product, cast(UnitPrice as varchar) as UnitPrice FROM #Products';

   SELECT @columnslist  = 'ISNULL ('   NAME   ','   ''''   ' '   ''''   ')'   ','
   FROM sys.dm_exec_describe_first_result_set(@SQLQuery, NULL, 0)


   SET @columnslist = left (@columnslist, Len (@columnslist) - 1)
   SET @FROMPOS = CHARINDEX ('FROM', @SQLQuery, 1)
   SET @restOfQuery = SUBSTRING(@SQLQuery, @FROMPOS, LEN(@SQLQuery) - @FROMPOS   1)
   SET @columnslist = Replace (@columnslist, '),', ') as TD,')
   SET @columnslist  = ' as TD'
   SET @DynTSQL = CONCAT (
         'SELECT (SELECT '
         , @columnslist
         ,' '
         , @restOfQuery
         ,' FOR XML RAW (''TR''), ELEMENTS, TYPE) AS ''TBODY'''
         ,' FOR XML PATH (''''), ROOT (''TABLE'')'
         )

   EXEC (@DynTSQL)
   --PRINT @DynTSQL;
   SET NOCOUNT OFF
END
GO

CodePudding user response：

I think this procedure is overly complicated, and uses dynamic SQL where it is simply not warranted.

We can do this much easier, by taking a query, adding FOR XML RAW, ELEMENTS, TYPE to it, and passing it to the procedure, then using XQuery to generate the table.

CREATE OR ALTER PROC dbo.usp_ConvertQuery2HTMLTable 
  @xml xml
AS

SELECT @xml.query('
<TABLE><TBODY>
{ for $row in /row
  return element TR {
              for $col in $row/*
              return element TD {
                    data($col)
                }
  }
}
</TBODY></TABLE>
');

You execute it like this:

DECLARE @xml xml = (
    SELECT Product, UnitPrice FROM #Products
    FOR XML RAW, ELEMENTS, TYPE
);

EXEC usp_ConvertQuery2HTMLTable @xml = @xml;

You can even add column headers. And it's probably even better as a Table Valued function.

CREATE OR ALTER FUNCTION dbo.ConvertQuery2HTMLTable 
(  @xml xml )
RETURNS TABLE
AS RETURN

SELECT HtmlTable = @xml.query('
<TABLE><TBODY>
<TR>
{
 for $colName in /row[1]/*
 return element TD { element b {local-name($colName)} }
}
</TR>
{
  for $row in /row
  return element TR {
              for $col in $row/*
              return element TD {
                    data($col)
                }
  }
}
</TBODY></TABLE>
');

You then execute very simply like this (note the double parenthesis)

SELECT *
FROM ConvertQuery2HTMLTable((
    SELECT Product, UnitPrice FROM #Products
    FOR XML RAW, ELEMENTS, TYPE
));

db<>fiddle

CodePudding user response：

Instead of cast(UnitPrice as varchar), you should use FORMAT (docs here) to convert the numeric to a currency string. It is even flexible enough to let you pick the appropriate locale formatting as well.

FORMAT(UnitPrice, 'C', 'en-us') AS 'UnitPrice'

CodePudding user response：

First, I had pasted a version of query where I had tried another fix ( that didn't work) - the CAST(). I did fix it by chaging ISNULL() to COALESCE()



   DECLARE @columnslist NVARCHAR (1000) = ''
   DECLARE @restOfQuery NVARCHAR (2000) = ''
   DECLARE @DynTSQL NVARCHAR (3000)
   DECLARE @FROMPOS INT


IF OBJECT_ID('tempdb..#Products') IS NOT NULL
  DROP TABLE #Products

CREATE TABLE #Products
(
   Product [varchar](50) NULL,
   UnitPrice [numeric](20, 6) NULL
)


INSERT INTO #Products
SELECT 'Nougat Creme', 14.00
UNION
SELECT 'Gorgonzola', 12.00
UNION
SELECT 'Ale', 14.00
UNION
SELECT 'Chocolate', 10.00
UNION
SELECT 'Lager', 12.00
UNION
SELECT 'Bread', 9.00

   DECLARE @SQLQuery NVARCHAR(3000) = 'SELECT Product, UnitPrice FROM #Products';

   SELECT @columnslist  = 'COALESCE('   NAME   ','   ''''   ' '   ''''   ')'   ','
   FROM sys.dm_exec_describe_first_result_set(@SQLQuery, NULL, 0)

   SET @columnslist = left (@columnslist, Len (@columnslist) - 1)
   SET @FROMPOS = CHARINDEX ('FROM', @SQLQuery, 1)
   SET @restOfQuery = SUBSTRING(@SQLQuery, @FROMPOS, LEN(@SQLQuery) - @FROMPOS   1)
   SET @columnslist = Replace (@columnslist, '),', ') as TD,')
   SET @columnslist  = ' as TD'

   SET @DynTSQL = CONCAT (
         'SELECT (SELECT '
         , @columnslist
         ,' '
         , @restOfQuery
         ,' FOR XML RAW (''TR''), ELEMENTS, TYPE) AS ''TBODY'''
         ,' FOR XML PATH (''''), ROOT (''TABLE'')'
         )


   PRINT (@DynTSQL)
   EXEC (@DynTSQL)