Provide column names as values to all null fields

[slobad23]

Hello all,

Once again I seek the advice of the genius collective from Tek-Tips.

The requirement I have is to test a number of stored procedures and views against tables that contain little to no data. In order to sanity check the results (and make sure my mappings are all correct) it would be good to have all fields that are currently null, populated with the column name.

So where I would current have something like this:

First Name, Last Name, Gender...
John, NULL, NULL

I would instead have:

First Name, Last Name, Gender...
John, Last Name, Gender

I would like to be able to do this for any number of tables so a function that looped through any given table and carried out this sort of logic would be ideal. I have a number of tables with a lot of columns I need to perform this on.

The environment is MS SQL Server 2008.

Thanks in advance,

Slobad23

 

[PWise]

how about isnull(lastname,'last name',lastname)

 

[pmurch]

Here's how I'd do it without having the specifically code the field names. I tested it on a table created exactly as yours (lastname, firstname, gender). Here you would need to specify the Catalog/DB and tablename, but if desired, these also could come from a cursor that reads the INFORMATION_SCHEMA table, pulling in the TABLE_NAME values and then processing the following code for each TABLE_NAME. Hope this helps.

if object_id('tempdb..#tmp') <> 0 drop table #tmp

declare @dbn nvarchar(100) = 'MyDB' -- Catalog/DB
declare @tbl nvarchar(100) = 'MyTbl' -- Table
declare @cmdout nvarchar(max)

set @cmdout = 'declare @fieldcur nvarchar(100), @cmd nvarchar(max) = ''select '' '
+ 'SELECT COLUMN_NAME AS ''Column'' INTO #tmp '
+ 'FROM ' + @dbn + '.INFORMATION_SCHEMA.COLUMNS '
+ 'WHERE TABLE_NAME = N''' + @tbl + ''' '
+ 'declare cur cursor for select [column] from #tmp '
+ 'open cur '
+ 'fetch next from cur into @fieldcur '
+ 'while @@FETCH_STATUS = 0 begin '
+ 'set @cmd = @cmd + '' isnull('' + @fieldcur '
+ ' + '', '''''' + @fieldcur + '''''') as '''''' + @fieldcur + '''''', '' '
+ 'fetch next from cur into @fieldcur '
+ 'end close cur deallocate cur '
+ 'set @cmd = LEFT(@cmd, len(@cmd) - 1) '
+ 'set @cmd = @cmd + '' from MyTbl'' '
+ 'exec sp_sqlexec @cmd '

exec sp_sqlexec @cmdout

Results:
FirstName LastName Gender
John LastName Gender

 

[pmurch]

Note on previous post: the opening check for existence of #tmp is superfluous. It was a hangnail from earlier testing...

 

[pmurch]

Ugh. One more "oops" during copying the code to the post window:

In the next to the last line, the word "MyTbl" should be replaced by the @tbl variable, making the correct line be:

+ 'set @cmd = @cmd + '' from ' + @tbl + ''' '

Sorry for the two quick corrections -- no peer review in my office!