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