ISNULL(fieldX,'') for all fields

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2012 Forums

Transact-SQL (2012)

ISNULL(fieldX,'') for all fields

Author

Topic

madlo
Starting Member

41 Posts

Posted - 2014-12-17 : 03:31:54

I have a current select linked to excel report via a sql view that has 500 columns.

However there are lots of nulls showing in the excel report that I want as blank instead.

Instead of putting ISNULL(fieldX,'') as fieldX 500 times is there not a way to do this in sql in a clever on the sql view.

madlo
Starting Member

41 Posts

Posted - 2014-12-17 : 06:02:11

Solution below


DECLARE @s VARCHAR(MAX)
DECLARE @t VARCHAR(100)
SELECT @t='dbo.MyTableName'

SELECT @s =  ISNULL(@s+', ','') + 'ISNULL(CAST(['+c.name   +'] as VARCHAR(100)),'''') as [' + c.name +']'+ CHAR(13)+CHAR(10) 
FROM sys.objects o
JOIN sys.schemas s ON o.[schema_id] = s.[schema_id]
JOIN sys.columns c ON o.[object_id] = c.[object_id]
WHERE  s.name +'.'+ o.name= @t
ORDER BY column_id

SELECT 'SELECT ' + @s + ' FROM ' + @t

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources