1. TSQL IsNull Function

In SQL Server, in some cases we need to replace a null value with more descriptive value. For example, while we select a record from a table, we can replace the null value from a column into a more meaningful value. The TSQL IsNull function can do the trick for us.

2. TSQL IsNull Example

Now, have a look at the below query:

We use Microsoft supplied Pubs sample database to fetch the records from Titles table. In the result, at record numbers 7 & 10, the royalty columns are showing null entries. We can change the query to get a meaningful value for this column value as shown below:

Explanation

1. Here we use the IsNull function to check the null column value. This function takes two parameters.
2. For the first parameter, we supply the column name which must be checked with the null value. In our case, we supplied the Royalty column.
3. Third parameter is the replacement value. IsNull function will check for Null value and when it finds the null, it will replace the null with this second parameter value.
4. In our case, we have two null values for the Royalty column and the result here shows the values are rightly converted into a numeric 0 to denote no royalty for those books.

3. TSQL IsNull Function Restrictions

When using the IsNull function, the replacement value should match with the data type of the first column. Look at the example below:

Explanation

1. Data Type of the Royalty column is int and we pass this as first parameter to the IsNull function.
2. Royalty Column and replacement values are not matching as the second argument is not an integer but a string.
3. When we execute the query, SQL Server reports the error stating ‘No. RY’ is not integer type.

4. Code Reference

• Anonymous Type in C#
• Null-Conditional Operator in C#

Categories: SQL Server

Tags: IsNull, TSQL

This site uses Akismet to reduce spam. Learn how your comment data is processed.