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

Go to Top of Page
   

- Advertisement -