Let's consider that we have a table that has several number of
columns and they might have null values. Now we need to get blank or zero
or anything else (whatever) in place of null values in the result set. For example
let's have table (TEST) with data like:
And when we need a result like:
The SQL query would be-
Select IsNull(Col1,'A')as Col1, IsNull(Col2,'A')as Col2, IsNull(Col3,'A') as Col3 From TEST
But here we use three
Isnull functions in the above query. But you have to write a
SQL query (in SQL Server 2005 or above) in which
there should be used only one ISNULL function (to avoid null values). And even query will be optimized if there will be less or
no use of loop.
A solution will be like:
Declare @ColName as Varchar(400)
SELECT @ColName= COALESCE( @ColName + ' ISNULL(' +c.name + ',''a'') ','', '') +
c.name + ', '
FROM sysobjects o
JOIN syscolumns c ON o.id = c.id
WHERE o.xtype = 'U'
AND (o.name ='NULLDEMO')
SEt @ColName= ( 'Select ' + SUBSTRING(@ColName,0,LEN(@ColName)-1) + ' FROM NULLDEMO ')
Here, we getting all column names of the table using the sysobjects table then we create
a SQL string (I did not use any loop) and then we create a query ...And execute it using