How to use IsNull in T-SQL


Enjoy the ongoing promo:

The IsNull method is a built-in function that takes two parameters. The first parameter is the expression or variable that the IsNull method checks and returns true if the variable contains Null. The second parameter is the replacement value that the IsNull method returns if the expression is Null.

Below is an instance of a table with Null 
 

















The student table contains student numbers and student names, and there are NULL in rows 3, 8 and 10.
Those three rows represent the case where there could be NULL in the database table.

Step 1.


To use the Isnull method, let us declare a variable StudentName and set it to Null.

Like this:  declare @StudentName as nvarchar(10) = Null

Then let use the Isnull method to check for the variable value, like this:
 
select Isnull(@StudentName, 'No name') as StudentName

By now you should know the result we are expecting right?

When we run the two lines of code together 


what happened is this:

If the StudentName is not equal to Null it returns the student name otherwise it returns No Name.

Step 2. 


The Isnull method can select all the Nulls in tblStudent1 and replace it with 'No Name'.





The code above replaces three rows 3, 8 and 10 in the student table with No Name instead of Null.

















This shows that you can also use the Isnull method to create table.

How to Use "IsNull"  Within CASE to Calculate Data from Tables.


For example, I will shown two tables here as A and B, this could be any table from your database.

CASE
WHEN ISNULL(A, 0)=0
THEN
ISNULL(A, 0)
WHEN
ISNULL(A, 0) > ISNULL(B, 0)
THEN
ISNULL(A, 0) - ISNULL(B, 0)
ELSE
ISNULL(B, 0) - ISNULL(A, 0)
END,
ISNULL(B, 0) AS B,

As shown above the is null can be use to calculate and get result from two or more table using the join.
The join part is not shown, but here we consider two tables A and B tables. 

Below is the analysis of what the code above does.

Case
When isNull return 0
Then the result is 0
Case 2
When A is not null and is greater than B
Then subtract B from A
else is the case when A is not null and is less than B
then subtract A from B.

Note that Isnull is faster than coalesce and it is also better than coalesce where you've got two different types of  data type.

No comments:

Post a Comment

Note: only a member of this blog may post a comment.

New Post

Create Store procedures

Store procedure has some similarities with views in the sense that it allows encapsulation. i.e. to retrieve for further, later use a series...