NULL Value in SQL Server 2012
In this article I will explain the NULL value with IsNull() and Coalesce() functions in SQL Server.
In this article, I will explain how to use and handle a Null value in SQL Server. A NULL value represents an unknown value. It's unknown in the sense that the value is missing. If you are a developer, you will almost certainly have to deal with NULL values at some point in a project when you work with tables, Stored Procedures etc. So let's have a look at a practical example of how to use and handle a Null value in SQL Server. The example is developed in SQL Server 2012 using the SQL Server Management Studio.
How to assign NULL values to variable
Now create a variable var and assign the NULL value to the variable.
DECLARE @Var int;
SET @Var = NULL;
NULL values with Arithmetic Operation
SELECT 5+null as Result;
NULL values with string Operation
SELECT 'Rohatash' + null as Result;
NULL values with Datetime Operation
SELECT getdate()+null as Result;
How to handle Null value in SQL Server
The IsNull() and Coalesce() functions are both used to replace null values with a user-defined value. The following defines how to handle a NULL value.
The ISNULL() function is used to replace NULL with the specified replacement value. This function contains only two arguments.
ISNULL (check_exp, change_value)
SELECT ISNULl(5+null, 5) as Result;
SELECT ISNULL('Rohatash' + null, 'Rohatash') as Result;
SELECT ISNULL(getdate()+null, getdate()) as Result;
The Coalesce() function returns the first non-null value among its arguments. This function doesn't limit the number of arguments, but they must all be of the same data type.
COALESCE ( expression [ ,...n ] )
SELECT COALESCE(5+null, 5) as Result;
SELECT COALESCE('Rohatash' + null, 'Rohatash') as Result;
SELECT COALESCE(getdate()+null, getdate()) as Result;