How to use IsNull in T-SQL

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 return 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.
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...