将money更改为数字将打破这种动态SQL方法

Question

这篇文章有一个对html"看起来干净优雅的方法。但是如果我将money数据类型更改为numeric，就会得到一个错误:

将数据类型varchar转换为数字

我的版本:

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

Answer 1

我认为这个过程过于复杂，并且在根本不需要的地方使用动态SQL。

我们可以更容易地做到这一点，通过获取查询，将FOR XML RAW, ELEMENTS, TYPE添加到其中，并将其传递给过程，然后使用XQuery生成表。

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>
');

你可以这样执行:

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

---

您甚至可以添加列标题。作为一个表值函数，它可能会更好。

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>
');

然后像这样简单地执行(注意双括号)

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

,db&lt的在小提琴

Answer 2

代替cast(UnitPrice as varchar)，您应该使用FORMAT(这里的文档)将数字转换为货币字符串。它甚至足够灵活，可以让您选择适当的区域设置格式。

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

Answer 3

首先，我粘贴了一个版本的查询，我已经尝试了另一个修复(没有工作)- CAST()。我确实通过将ISNULL()更改为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)