SQL Server ISNULL() With Multi Column Names

Image2-IsNull-With-Single-Column

Limitation of IsNull() function

The IsNull function can check only if one value is null. It cannot check null for multiple values. That means it is not capable of handling the functionality of checking if the first parameter is null and then moving on to check the next parameter for null.

Now assume that for report generation, we want to get the passport number or license number or pan number etc. for reference purposes. If a passport number is null, then we need to extract the license number. If the license number is null then pan, if a pan is null then account number. If all are null then we need to flag a message as ‘Invalid’.

The problem is that IsNull function here needs to be used in a nesting manner.

Let us see the ISNULL being used in a select statement in a nested fashion

Image3-IsNull-Multiple-Columns

In the above select statement, we have used IsNull function 5 times to get the desired output.

ISNULL vs Coalesce Function

There is an alternative way using another SQL Server function known as Coalesce.

Syntax of Coalesce

Coalesce can take multiple parameters. It returns the first not null parameter. If all the parameters are null, then it will return the default parameter.

In other words, we can say that coalesce behaves as per the following syntax as well

So instead of nesting IsNull function multiple times, we can use a single coalesce function and get the same output as shown here

Image4-Coalesce