SQL Server中money改numeric后动态SQL生成HTML表格报错如何解决

报错原因 #

converting data type varchar to numeric 报错的核心诱因是ISNULL函数的参数类型不匹配：你现有拼接逻辑中，给ISNULL传入的默认占位值是空格字符串（字符类型），如果查询结果包含numeric类型的列，SQL Server会自动尝试将空格字符串隐式转换为numeric类型，而空格不属于合法数值，因此触发转换报错。

修复方法 #

修改@columnslist的拼接逻辑，先将所有列统一显式转换为字符串类型，再做非空判断，即可兼容所有可转字符串的字段类型，修改后的完整存储过程代码如下：

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, UnitPrice FROM #Products';

   -- 此处修改：增加CAST转字符串逻辑，避免类型不匹配
   SELECT @columnslist += 'ISNULL (CAST(' + NAME + ' AS NVARCHAR(1000)), '' '')' + ','
   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

修改后你可以直接移除@SQLQuery中对UnitPrice的手动转字符串逻辑，存储过程已经可以自动兼容数值类型字段。

内容的提问来源于stack exchange，提问作者Chris Harrington