How to Use Isnull to All Column Names in SQL Server 2008

How Can we use ISNULL to all Column Names in SQL Server 2008?

You can use ISNULL multiple times in the same SQL statement for different columns, but you must write it separately for each column:

SELECT
    ISNULL(ProductName, 'No Data') AS ProductName,
    ISNULL(CAST(UnitPrice AS NVARCHAR), 'No Data') AS UnitPrice, 
    ISNULL(CAST(UnitsInStock AS NVARCHAR), 'No Data') AS UnitsInStock,
    ISNULL(CAST(UnitsOnOrder AS NVARCHAR), 'No Data') AS UnitsOnOrder
FROM tbl

If you are building a dynamic SQL query, you could theoretically gather a list of columns in the table and generate a query with ISNULL on each one. For example:

DECLARE @SQL nvarchar(max)

SET @SQL = 'SELECT '

SELECT @SQL = @SQL + 'ISNULL(CAST([' + sc.name + '] AS NVARCHAR), ''No Data'') AS [' + sc.name + '],'
FROM sys.objects so
INNER JOIN sys.columns sc ON sc.object_id = so.object_id
WHERE so.name = 'tbl'

-- Remove the trailing comma
SELECT @SQL = LEFT(@SQL, LEN(@SQL) - 1) + ' FROM tbl'

EXEC sp_sqlexec @SQL

This code has problems when converting some column types like timestamps to an nvarchar, but it illustrates the technique.

Note that if you had another column that should be returned if a value is null, you could use the COALESCE expression like this:

SELECT COALESCE(ProductName, P_Id) AS Product...

Is there a better way to apply isnull to all columns than what I'm doing?

You could create a VIEW against the tables in question where the ISNULL logic you want is set up. Then queries against the views would return the data you want.

EDIT:

As requested, some sample code to accomplish creating the VIEWs automatically. This is pretty gross, but for something that only has to be run once it will work. Beware of type issues (you stated everything should transmute to 0 so I assume all your columns are of a suitable numeric type):

DECLARE @table_def varchar(max)
SET @table_def = 'CREATE VIEW <tname>_NoNull AS SELECT '

SELECT @table_def = REPLACE(@table_def, '<tname>', t.name) + 
    'ISNULL(' + c.name + ', 0) AS ' + c.name + ', '
FROM sys.tables t
    INNER JOIN sys.columns c ON t.object_id = c.object_id
WHERE t.name = <<table name>>

SELECT @table_def

SQL Server 2008 - Set a value when the column is null

You want to use the COALESCE function.

SELECT
    Name
,   DOB
,   COALESCE(Address1, 'NA')
,   COALESCE(Address2, 'NA')
,   COALESCE(City, 'NA')
,   COALESCE(State, 'NA')
,   COALESCE(Zip, 'NA')
FROM Users

ISNULL in SQL Server 2008

– Original statement

SELECT FirstName + ' ' + LastName + ' '+ ISNULL(MI + '.', '') As MiddleInitial 
FROM CS_tblMaster WHERE CustomerNo = 2627240

– My original answer (edited)

SELECT FirstName + ' ' + LastName + ' '+ (ISNULL(MI, '') + '.') As MiddleInitial 
FROM CS_tblMaster WHERE CustomerNo = 2627240

In this case, SQL would first check to see if MI is Null and uses MI if Not Null or uses an empty string if it is. Then it concatenates that result, which is now never Null, with the period.

– Final answer

SELECT 
    FirstName + ' ' + LastName + ' '
        + CASE WHEN MI IS NULL THEN '' ELSE MI + '.' END As MiddleInitial 
FROM CS_tblMaster WHERE CustomerNo = 2627240

@Satish, not sure if you feel you have answer yet since you haven't selected one, and I apologize if my answer was short and fast. Seeing all the responses made me realize I hadn't thought much about your question when I first saw it.

To answer “I would like to understand of this implementation”, Nulls are a completely special value in SQL. Not an empty string, not spaces, not zeros. They mean in a more literal sense “nothing”. You can check for them, can see if something is null. But you can't “do” things with Nulls. So 57 + Null = Null. 'Mary' + Null = Null. ((12 *37) +568) / Null = Null. The Max() of 'Albert', 'Mary', Null, and 'Zeke' is Null. This article http://en.wikipedia.org/wiki/Null_(SQL) may help, with a decent description in the section on Null Propagation.

The Isnull function is not so much a test for Null, but a way to handle it. So to test if something is Null you would use ColumnName Is Null or ColumnName Is Not Null in your Select. What Isnull(MI,'') says is: I want a value, if the value of MI it not null, then I want MI, otherwise I want an empty string.

Going on, I'm not sure I initially understood what you were actually trying to do. If you were trying to get a period when the middle initial was null, then my original answer and most of the others would work for you. But I think you may be trying to say: If I have a middle initial, then I want the middle initial followed by a period. If I don't have middle initial then I want nothing: 'Alberto C. Santaballa' or 'Alberto Santaballa', never 'Alberto . Santaballa”. If that is the case then use the final statement in the edited answer.

@Zec, thanks for the edit. The typo was another product of too-fast typing! :-/

SQL Server ISNULL multiple columns

If you want to output a row for some condition (or requested values ) and output a row when it does not meet condition,
you can set a pseudo table for your requested values in the FROM clause and make a left outer join with your Table1.

SELECT ISNULL(Table1.DatasourceId, 999999), 
       Table1.AgencyId,
       Table1.AccountingMonth, 
       COUNT(*) as count
FROM ( VALUES (5, 4, 201907 ),
              (6, 4, 201907 ))
       AS requested(DatasourceId, AgencyId, AccountingMonth)
LEFT OUTER JOIN Table1 ON requested.agencyid=Table1.AgencyId
        AND requested.datasourceid = Table1.DatasourceId
        AND requested.AccountingMonth = Table1.AccountingMonth
GROUP BY Table1.DatasourceId, Table1.AgencyId, Table1.AccountingMonth

Note that:

I have put a ISNULL for the first column like you did to output a particular value (9999) when no value is found.
I did not put the ISNULL(...,NULL) like your query in the other columns since IMHO it is not necessary: if there is no value, a null will be output anyway.
I added a COUNT(*) column to illustrate an aggregate, you could use another (SUM, MIN, MAX) or none if you do not need it.
The set of requested values is provided as a constant table values (see https://learn.microsoft.com/en-us/sql/t-sql/queries/table-value-constructor-transact-sql?view=sql-server-2017)

I have added multiple rows for requested conditions : you can request for multiple datasources, agencies or months in one query with one line for each in the output.

If you want only one row, put only one row in "requested" pseudo table values.
There must be a GROUP BY, even if you do not want to use an aggregate (count, sum or other) in order to have the same behavior as your distinct clause , it restricts the output to single lines for requested values.

Check multiple columns for null in SQL Server 2008

Nested Isnull

Isnull(DateCreated,isnull(DateModified, getdate()))

Or use a CASE

case when DateCreated is null and DateModified is null then getdate()
     when DateCreated is null then DateModified 
     else DateCreated end

ISNULL with SQL Server 2008

Change your dynamic SQL to:

EXEC ('ALTER TABLE #tbl ADD [' + @bno + '] varchar(30) NULL DEFAULT(''NA'')')

How to count in SQL all fields with null values in one record?

declare @T table
(
  ID int,
  Name varchar(10),
  Age int,
  City varchar(10),
  Zip varchar(10)
)  

insert into @T values 
(1, 'Alex', 32, 'Miami', NULL),
(2,  NULL,  24,  NULL,   NULL)

;with xmlnamespaces('http://www.w3.org/2001/XMLSchema-instance' as ns)
select ID,
       (
          select *
          from @T as T2
          where T1.ID = T2.ID
          for xml path('row'), elements xsinil, type 
       ).value('count(/row/*[@ns:nil = "true"])', 'int') as NullCount
from @T as T1

Result:

ID          NullCount
----------- -----------
1           1
2           3

Update:

Here is a better version. Thanks to Martin Smith.

;with xmlnamespaces('http://www.w3.org/2001/XMLSchema-instance' as ns)
select ID,
       (
          select T1.*
          for xml path('row'), elements xsinil, type 
       ).value('count(/row/*[@ns:nil = "true"])', 'int') as NullCount
from @T as T1

Update:

And with a bit faster XQuery expression.

;with xmlnamespaces('http://www.w3.org/2001/XMLSchema-instance' as ns)
select ID,
       (
          select T1.*
          for xml path('row'), elements xsinil, type 
       ).value('count(//*/@ns:nil)', 'int') as NullCount
from @T as T1

ISNULL function in SQL Server 2008 not working properly

If you have no entry for the ID 1589, then in the DELETED table there will be no record, if you have it then it should return 1589.

So if you don't have I think it simple returns nothing, because this statement has no input row:

SELECT CAST(ISNULL([Id], -1) AS BIGINT) AS N'RetValId' FROM @result;

(If you SELECT * from @result it should be no rows there)

The second one return the -1 because you set first to the variable which is getting the NULL value after the select.

DECLARE @mi BIGINT;
SET @mi = (SELECT [Id] FROM @result)

(If you select only @mi after this, then it should be NULL)

I think that is the explanation

UPDATED:

May you can try a small trick to achive it without an other varriable:

SELECT CAST(ISNULL(MAX([ID]),-1) AS BIGINT) AS N'RetValId' FROM @result;

Because of MAX the insie statement will be NULL, so here is the trick. If something was deleted, then the ID will be there.
I hope it helped.

SQL: Using ISNULL with dynamic pivot

I would set your query up slightly different because while it is dynamic in that the column names are changing, you have still hard-coded the number of columns.

First, I would use a recursive CTE to generate the list of months/years that you want to create.

DECLARE @startDate datetime

SET @startDate = '2013-01-01'

;with dates as
(
  select @startdate datelist, 1 sp
  union all
  select dateadd(month, 1, datelist), sp+1
  from dates
  where sp+1 <= 5 -- change this number 5 to the number of months you need
)
select   sp,
  REPLACE(SUBSTRING(CONVERT(varchar(11), datelist, 13), 4, 8), ' ', '') MONTHANDYEAR
from dates

See SQL Fiddle with Demo. This is going to create your list of the 5 months with the year automatically. Then you are not hard-coding the 5 columns. Your current query is not as flexible as it could be. What will happen if you then want 12 months, you are going to have to change your code.

Once you generate the list of dates, I would insert it into a temp table so you can use it to get the columns.

The code to get the list of columns is:

select @cols = STUFF((SELECT ',' + QUOTENAME(monthandyear) 
                    from #datesTemp
                    group by monthandyear, sp
                    order by sp
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

select @colNames = STUFF((SELECT  ', isnull(' + QUOTENAME(monthandyear)+', 0) as '+QUOTENAME(monthandyear)
                    from #datesTemp
                    group by monthandyear, sp
                    order by sp
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

See SQL Fiddle with Demo. You will see that there are two versions. The first one @cols gets the list of columns that will be used in the pivot. The second @colNames will be used in the final SELECT list to replace the null values with the zeros.

Then you put it all together and the code will be: (Note: I am using a version of my answer from your previous question)

DECLARE @cols AS NVARCHAR(MAX),
    @colNames AS NVARCHAR(MAX),
    @query AS NVARCHAR(MAX),
    @startDate datetime

SET @startDate = '2013-01-01'

;with dates as
(
  select @startdate datelist, 1 sp
  union all
  select dateadd(month, 1, datelist), sp+1
  from dates
  where sp+1 <= 5 -- change this number 5 to the number of months you need
)
select   sp,
  REPLACE(SUBSTRING(CONVERT(varchar(11), datelist, 13), 4, 8), ' ', '') MONTHANDYEAR
into #datesTemp
from dates

select @cols = STUFF((SELECT ',' + QUOTENAME(monthandyear) 
                    from #datesTemp
                    group by monthandyear, sp
                    order by sp
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

select @colNames = STUFF((SELECT  ', isnull(' + QUOTENAME(monthandyear)+', 0) as '+QUOTENAME(monthandyear)
                    from #datesTemp
                    group by monthandyear, sp
                    order by sp
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = 'SELECT resource, clientname,' + @colNames + ' 
             from 
             (
                select [CLIENTNAME], [RESOURCE], [FORECASTTOTAL],
                   REPLACE(SUBSTRING(CONVERT(varchar(11), SCHEDULEDDATE, 13), 4, 8), '' '', '''') monthandyear
                from viewprojscheduling_group
            ) x
            pivot 
            (
                sum(FORECASTTOTAL)
                for monthandyear in (' + @cols + ')
            ) p '

execute(@query)

See SQL Fiddle with Demo. This query will give you the result:

| RESOURCE | CLIENTNAME | JAN2013 | FEB2013 | MAR2013 | APR2013 | MAY2013 |
---------------------------------------------------------------------------
|     res1 |        abc |    1000 |    2000 |       0 |       0 |       0 |
|     res1 |        def |       0 |       0 |    2000 |       0 |       0 |
|     res2 |        def |    1500 |       0 |       0 |       0 |       0 |
|     res3 |        ghi |       0 |       0 |    2500 |       0 |       0 |