question

MatePataki-7964 avatar image
0 Votes"
MatePataki-7964 asked MelissaMa-msft answered

dynamic updates issue

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?


sql-server-transact-sql
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Viorel-1 avatar image
1 Vote"
Viorel-1 answered

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


5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

OlafHelper-2800 avatar image
0 Votes"
OlafHelper-2800 answered MatePataki-7964 commented

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



· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

This is why I counted the chars and used different datatypes. OK, overlook from the print() I am not able to execute the command, since I have the same error message, but they are definitely under 8000 chars.

0 Votes 0 ·

I have added a new clause to the code

  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
 AND INFORMATION_SCHEMA.COLUMNS.ORDINAL_POSITION <= 3

and this what I found:

for the two first column, the code is possible the command properly, but when it reaches the last column then the "='';" won't be populated

 UPDATE db.[dbo].table_X SET [Column_1] = NULL WHERE [Column_1] = ''; 
 UPDATE db.[dbo].table_X SET [Column_2] = NULL WHERE [Column_2] = ''; 
 UPDATE db.[dbo].table_X SET [Column_3] = NULL WHERE [Column_3]
0 Votes 0 ·
TomCooper-6989 avatar image
2 Votes"
TomCooper-6989 answered

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

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

MelissaMa-msft avatar image
1 Vote"
MelissaMa-msft answered

Hi @MatePataki-7964,

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.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.