How do I properly include an IIF Statement in a Dynamic SQL Statement without getting an Error

47 Views Asked by At

The code I have below was working, it was creating more code that created code that set-up the extended properties for a given table. But we wanted to start pulling pre-existing column descriptions from another table if there was a match. I did that with a left join and a IIF statement, which works fine in a pure SQL statement. BUT when I port it over into the Dynamic SQL version of the statement I get an error in the line with the IIF statement.

The error message is:

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

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

This is the old version of the code without the IIF statement that DOES work:

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
0

There are 0 best solutions below