I have a table, let's call it "table_X" in that table I have multiple columns (46) and in the future there is a possibility that we will expand it to have more columns, since the source of a table is an old ERP system, we need to transform the dataset in some cases, one of the transformation is that when we replace the '' values with NULLs and here is where I have problem, I wrote a dynamic update, because the previously mentioned reason (in the future we will have more columns), but I got error message and right now I am stuck.
DECLARE @SQL_columnnull NVARCHAR(max)
DECLARE @db2 NVARCHAR(max)
DECLARE @table2 NVARCHAR(max)
SET @db2 = 'db'
SET @table2 = 'table_X'
SELECT @SQL_columnnull_part_1 = STRING_AGG(CAST( N' UPDATE '+@db2+'.[dbo].'+@table2+' WITH (TABLOCK) SET ' AS NVARCHAR(MAX))
+QUOTENAME(COLUMN_NAME,'['']') + N' = NULL WHERE '
+QUOTENAME(COLUMN_NAME,'['']') ,+ N' = '''';')
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @table2
PRINT(@SQL_columnnull)
EXEC(@SQL_columnnull)
The error message:
An expression of non-boolean type specified in a context where a condition is expected, near 'action type'.
The problem is that it looks like when the code above reach the last column it is not able to list all of the columns, I have counted all of the characters and printed message is 3,999 char long. After that I thought that I need to modify to @sql_columnnull datatype to varchar to increase the length to 8,000, but still have the same issue, but it looks better with 6,333 characters.
This is how the last part should have look:
UPDATE db.[dbo].table_X WITH (TABLOCK) SET [action type] = NULL WHERE [action type] = '';
However when the code reach the last column it looks like this (in the print):
UPDATE db.[dbo].table_X WITH (TABLOCK) SET [action type] = NULL WHERE [action type]
What is the problem with the code?