0

This link is where I found part of an answer to my problem.

SQL replace all NULLs

Simon posted

"Be a boss. Write something like:

select 'update ' + table_name + ' set [' + column_name + '] = '''' where [' + column_name + '] is null'
from tempdb.information_schema.columns
where table_name = 'YourTableName'

It'll spit out a big ol' query for you. You're welcome"

But I would like to know if there a way to use the results set in a parameter and execute all of the update statements. I tried something like this 

DECLARE @sql2 AS NVARCHAR(MAX) = N' 
SELECT ''UPDATE '' + table_name + '' SET ['' + column_name + ''] = '''''''' WHERE ['' + column_name + ''] IS NULL''
FROM tempdb.information_schema.columns
WHERE table_name = ''##tempF'''

EXEC sp_executesql @stmt = @sql2;

DECLARE @sql3 AS NVARCHAR(MAX);
SET @sql3 = (SELECT @sql2);

EXEC sp_executesql @stmt = @sql3;

but it two result sets like listed below:

UPDATE ##tempF SET [claimid] = '' WHERE [claimid] IS NULL
UPDATE ##tempF SET [hdr_status] = '' WHERE [hdr_status] IS NULL
UPDATE ##tempF SET [memid] = '' WHERE [memid] IS NULL

Many thanks to you all. Cheers! Tim

Community
  • 1
  • 1
Tim Harms
  • 1

1 Answers1

0

Like this

--initialize variables
DECLARE @UpdateColumns varchar(max) = '' 
DECLARE @IsNullColumns varchar(max) = ''

SELECT 
    @UpdateColumns = @UpdateColumns + ',[' + COLUMN_NAME + '] = ISNULL([' + COLUMN_NAME + '],'''')',
    @IsNullColumns = @IsNullColumns + ' OR [' + COLUMN_NAME + '] IS NULL'
FROM tempdb.information_schema.columns
WHERE table_name = '##tempF'

This should fill in the two variables with the following values:

@UpdateColumns = ',[claimid] = ISNULL([claimid],''''),[hdr_status] = ISNULL([hdr_status],''''),[memid] = ISNULL([memid],'''')'
@IsNullColumns = ' OR [claimid] IS NULL OR [hdr_status] IS NULL OR [memid] IS NULL'

Then you need to assemble it all (remember to remove the first characters of each of the variables (the STUFF function is great for that):

DECLARE @qry varchar(max) = 'UPDATE ##tempF SET ' 
+ STUFF(@UpdateColumns,1,1,'') + ' WHERE ' 
+ STUFF(@IsNullColumns,1,4,'') --the 4 in here is to get rid of ' OR ' (4 chars)

EXEC(@qry)
Joao Leal
  • 5,533
  • 1
  • 13
  • 23
  • Hi Tim, the `@qry` variable should be enough to store everything has varchar(max) stores 2billion characters if I'm correct. The problem is when you use `PRINT(@qry)` management studio only prints the first 8000 characters (or something like that). If you want to see the query you can try to do multiple prints: `PRINT(@qry) PRINT(SUBSTRING(@qry,8000,8000)) PRINT(SUBSTRING(@qry,16000,8000))`. If it still doesn't work you can wrap this in a while loop and do it in batches of 10 or 20 columns – Joao Leal Jul 09 '13 at 22:27
  • Hello Joao, the reason I ask is I am getting an error message Msg 1038, Level 15, State 4, Line 2 An object or column name is missing or empty. For SELECT INTO statements, verify each column has a name. For other statements, look for empty alias names. Aliases defined as "" or [] are not allowed. Change the alias to a valid name. – Tim Harms Jul 09 '13 at 22:52
  • Just found a bug on my answer, the @IsNullsColumns is incorrect, please check the edited answer – Joao Leal Jul 09 '13 at 22:55
  • Hello I found the issue by using the print method, all of the column names were not in the where statement due to the following code IsNullColumns = IsNullColumns + ' OR [' + + '] IS NULL' So I added in the Column_name and it works!! Thanks so much!! – Tim Harms Jul 09 '13 at 22:57
  • Nice! You should always use the PRINT(@qry) command when using dynamic queries for debugging purposes. – Joao Leal Jul 09 '13 at 22:58