Using IsNull with different data types in SQL SERVER

Question

Using IsNull with different data types in SQL SERVER

Salil Singh 60

Hi Team,

I am working on on premises SQL Server. I have a requirement where I need to apply left outer join between 2 tables and use IsNull to show default data when columns are having null. Under ISNULL, if a column is null what shall i put for below data types -

Varchar.
Int.
Decimal (6,0)
Date
DateTime

Please let me know if any other info required from me.

Thanks,

Salil

2 answers

Your answer

Answer 1

Erland Sommarskog 121.3K MVP Volunteer Moderator

For varchar, you would typically use the empty string:

isnull(stringcol, '')

For remaining data types, you would need to ask your business stakeholders what they want to see.

For numeric values 0 may make sense, but it can also be grossly misleading.

Many times, you want the value to be blank in the presentation layer, and the simplest way to achieve that is to return NULL and let the presentation layer do the job.

If you have a very primitive presentation layer, like SSMS, one alternative is to convert everything to string so that you can use a blank string to represent the NULL value. But this makes the code bulky, and also inflexible, particularly for date/time values, where you will have a fixed formatting. Date/time values are best formatted in the presentation layer according to the user's regional settings, so that one user can see 2025-06-01, and another can see 01/06/2025.

Salil Singh 60 Reputation points

2025-06-07T05:58:42.77+00:00

HI @Erland Sommarskog ,

Thank you for above reply.

Could you please provide examples for Date and DateTime2 use(with ISNULL). I am looking for standard way to use it.

Thanks,

Salil
Erland Sommarskog 121.3K Reputation points MVP Volunteer Moderator

2025-06-07T08:17:09.7266667+00:00

The standard way is what I said: Return NULL to the presentation layer and have the presentation layer to deal with it. What drives you think this should be done in the database?
Balmukund 176 Reputation points Microsoft Employee

2025-06-08T02:14:32.85+00:00

+1 to @Erland Sommarskog

what value you want to show in the UI totally depends on the business. If I am looking at a dashboard, I would prefer to see empty rather than fake values. So, it depends on the business leaders, what they want to see and it's not a database problem.
Balmukund 176 Reputation points Microsoft Employee

2025-06-08T02:15:43.4+00:00

my comment was about specifically about date data type. Rest all can be empty or zero.
Erland Sommarskog 121.3K Reputation points MVP Volunteer Moderator

2025-06-08T09:27:23.51+00:00
Rest all can be empty or zero.

I would disagree on that. Zero can be a correct way to represent a NULL value. For instance take this query:

SELECT SUM(amount) FROM accounts WHERE customer = 'Jane'

Jane does not have any accounts, so the query returns NULL. But that is obviously the same as her standing being 0 €.

On the other hand, consider a query that returns data about people, and one item return is the age, computed from the birth date. However, the birth date is not a mandatory to enter, so it will be NULL for some people. In this representing NULL as 0 would be very funny - not the least if the list of people can include newborns. Clearly it should be presented as empty in the presentation layer (or some other non-numeric value).
Balmukund 176 Reputation points Microsoft Employee

2025-06-08T12:54:30.93+00:00

@Erland Sommarskog your example makes sense.
Salil Singh 60 Reputation points

2025-06-08T17:11:31.3833333+00:00

Hi @Erland Sommarskog , @balmukund

Thank you for above replies.

My requirement is bit different, none of the rows with Null columns will be shown to UI, such rows will be deleted from a different DB. Its a recent change where my SP now along with proper values would be generating Null values (for same columns). As C# code, earlier was getting returned values from SP as non null so chances are that receiving datatype in C# code was defined in a way to not handle null values, so I thought of making sure to handle null value in SP itself. Assigning a value to these null columns will not have any impact on UI as these such rows are to be deleted from a different DB.

Thanks,

Salil
Erland Sommarskog 121.3K Reputation points MVP Volunteer Moderator

2025-06-08T17:18:01.8+00:00

That sounds even worse. You cannot invent values in the database. You will need to investigate what that C# code is doing in more detail, and particularly, you need to get full understanding of what it is doing with the columns from these left-joined tables.

Answer 2

SUMIT RAY 0

ISNULL(column, default)

Share via

Using IsNull with different data types in SQL SERVER

2 answers

Your answer