dynamic updates issue

Question

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 @alenzi _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?

Answer 1

Since you want to build a list of statements separated by ';', then consider this usage of STRING_AGG too:

SELECT @SQL_columnnull_part_1 = 
    STRING_AGG( 
        concat(N' UPDATE ', @db2, N'.[dbo].', @table2, ' WITH (TABLOCK) SET ',
                QUOTENAME(COLUMN_NAME), N' = NULL WHERE ',
                QUOTENAME(COLUMN_NAME), N' = '''''),
        N'; ')
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @table2
AND INFORMATION_SCHEMA.COLUMNS.ORDINAL_POSITION <= 3

Answer 2

STRING_AGG is designed to produce a delimited list. It concatenates the items putting a delimiter between each item. It does not put a delimiter after the last item. In your case, the delimiter is N' = '''';'. Since you want a delimiter after the last item, you need the add a final delimiter. So the code should be

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' = '''';') + N' = '''';'
 FROM INFORMATION_SCHEMA.COLUMNS
 WHERE TABLE_NAME = @table2

Tom

Answer 3

Hi @Mate Pataki ,

Welcome to Microsoft Q&A!

You could also use STUFF instead of STRING_AGG as below:

 SELECT @SQL_columnnull_part_1  = STUFF((   
 SELECT ';'+ N' UPDATE '+@db2+'.[dbo].'+@table2+' WITH (TABLOCK) SET '    
 +QUOTENAME(COLUMN_NAME,'['']')  + N' = NULL WHERE '  
 +QUOTENAME(COLUMN_NAME,'['']') + N' = '''''  
 FROM INFORMATION_SCHEMA.COLUMNS  
 WHERE TABLE_NAME = @table2   
 FOR XML PATH('') ), 1, 1, '')  

Best regards
Melissa

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 4

What is the problem with the code?

You are on the wrong track, PRINT output maximum varchar(8000) or nvarchar(4000), see PRINT (Transact-SQL) => Remarks