如何在动态SQL语句中正确使用IIF语句以避免报错？

问题背景 #

原本的代码可正常生成设置指定表扩展属性的SQL语句，为了从另一表匹配提取已有列描述，添加左连接和IIF语句后，纯SQL逻辑运行正常，但移植到动态SQL版本时出现类型不兼容错误。

错误信息 #

Msg 402, Level 16, State 1, Line 75
The data types nvarchar(max) and sql_variant are incompatible in the add operator.

出错的动态SQL代码 #

DECLARE @SProcAdd varchar(100) = 'sys.sp_addextendedproperty'
DECLARE @SProcUpdate varchar(100) = 'sys.sp_updateextendedproperty'
DECLARE @SQLTableAdd varchar(max) = ''
DECLARE @SQLColumnAdd varchar(max) = ''
DECLARE @SQLTableUpdate varchar(max) = ''
DECLARE @SQLColumnUpdate varchar(max) = ''
DECLARE @Table varchar(200) = 'Table_Name_Here'

SELECT  
@SQLColumnAdd =  CONCAT(@SQLColumnAdd + ' ' + CHAR(13) + '--*** COLUMN NAME: ' + ' ' + sysc.COLUMN_NAME + '   ' +
    '***********************************************************************************' 
    + CHAR(13) + 'EXEC ' + @SProcAdd + ' 
     @level0type=N''SCHEMA'', @level0name=N''' + a.TABLE_SCHEMA + ''' --assigns schema
    ,@level1type=N''TABLE'', @level1name=N''' + a.TABLE_NAME + ''' --assigns table
    ,@level2type=N''COLUMN'', @level2name=N''' + sysc.COLUMN_NAME + ''' --assigns column
    ,@name=N''DESCRIPTION'', @value=N''' + IIF(mcd.ColumnDescription = NULL,'Enter_Description_Here', mcd.ColumnDescription) + ''' -- <------------------------ UPDATE COLUMN DESCRIPTION HERE!' + CHAR(13) + '', '')
FROM OUR_SERVER.INFORMATION_SCHEMA.TABLES a
INNER JOIN OUR_SERVER.INFORMATION_SCHEMA.COLUMNS sysc on a.TABLE_NAME=sysc.TABLE_NAME 
LEFT JOIN OUR_SERVER.MetaPERC.MasterColumnDescription mcd on sysc.COLUMN_NAME = mcd.ColumnName
WHERE a.TABLE_NAME LIKE @Table
ORDER BY ORDINAL_POSITION

PRINT @SQLColumnAdd

原本正常运行的代码（无IIF语句） #

SELECT  
@SQLColumnAdd =  CONCAT(@SQLColumnAdd + ' ' + CHAR(13) + '--*** COLUMN NAME: ' + ' ' + sysc.COLUMN_NAME + '   ' +
    '***********************************************************************************' 
    + CHAR(13) + 'EXEC ' + @SProcAdd + ' 
     @level0type=N''SCHEMA'', @level0name=N''' + a.TABLE_SCHEMA + ''' --assigns schema
    ,@level1type=N''TABLE'', @level1name=N''' + a.TABLE_NAME + ''' --assigns table
    ,@level2type=N''COLUMN'', @level2name=N''' + sysc.COLUMN_NAME + ''' --assigns column
    ,@name=N''DESCRIPTION'', @value=N'' CASE WHEN mcd.ColumnDescription = NULL THEN ''Enter_Description_Here'' ELSE mcd.ColumnDescription END'' -- <------------------------ UPDATE COLUMN DESCRIPTION HERE!' + CHAR(13) + '', '')
FROM OUR_SERVER.[INFORMATION_SCHEMA].[TABLES] a
INNER JOIN OUR_SERVER.[INFORMATION_SCHEMA].[COLUMNS] sysc on a.TABLE_NAME=sysc.TABLE_NAME 
LEFT JOIN OUR_SERVER.MetaPERC.MasterColumnDescription mcd on sysc.COLUMN_NAME = mcd.ColumnName
WHERE a.TABLE_NAME LIKE @Table
ORDER BY ORDINAL_POSITION

解决方案 #

1. 错误原因：mcd.ColumnDescription的数据类型是sql_variant，在和nvarchar(max)类型的字符串拼接时，两种类型不兼容导致报错；另外SQL中判断NULL不能用=，必须用IS NULL。
2. 修复步骤：
   • 用CAST或CONVERT把mcd.ColumnDescription显式转换为nvarchar(max)类型；
   • 把IIF(mcd.ColumnDescription = NULL, ...)改为IIF(mcd.ColumnDescription IS NULL, ...)。

修正后的代码 #

DECLARE @SProcAdd varchar(100) = 'sys.sp_addextendedproperty'
DECLARE @SProcUpdate varchar(100) = 'sys.sp_updateextendedproperty'
DECLARE @SQLTableAdd varchar(max) = ''
DECLARE @SQLColumnAdd varchar(max) = ''
DECLARE @SQLTableUpdate varchar(max) = ''
DECLARE @SQLColumnUpdate varchar(max) = ''
DECLARE @Table varchar(200) = 'Table_Name_Here'

SELECT  
@SQLColumnAdd =  CONCAT(@SQLColumnAdd + ' ' + CHAR(13) + '--*** COLUMN NAME: ' + ' ' + sysc.COLUMN_NAME + '   ' +
    '***********************************************************************************' 
    + CHAR(13) + 'EXEC ' + @SProcAdd + ' 
     @level0type=N''SCHEMA'', @level0name=N''' + a.TABLE_SCHEMA + ''' --assigns schema
    ,@level1type=N''TABLE'', @level1name=N''' + a.TABLE_NAME + ''' --assigns table
    ,@level2type=N''COLUMN'', @level2name=N''' + sysc.COLUMN_NAME + ''' --assigns column
    ,@name=N''DESCRIPTION'', @value=N''' + IIF(mcd.ColumnDescription IS NULL,'Enter_Description_Here', CAST(mcd.ColumnDescription AS nvarchar(max))) + ''' -- <------------------------ UPDATE COLUMN DESCRIPTION HERE!' + CHAR(13) + '', '')
FROM OUR_SERVER.INFORMATION_SCHEMA.TABLES a
INNER JOIN OUR_SERVER.INFORMATION_SCHEMA.COLUMNS sysc on a.TABLE_NAME=sysc.TABLE_NAME 
LEFT JOIN OUR_SERVER.MetaPERC.MasterColumnDescription mcd on sysc.COLUMN_NAME = mcd.ColumnName
WHERE a.TABLE_NAME LIKE @Table
ORDER BY ORDINAL_POSITION

PRINT @SQLColumnAdd

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