NULL can be produce weird results and sometimes it is hard to diagnose the root cause. So to avoid such a problem like this you need to remember few things before you write a query.
There are several ways to overcome this solution here I will discuss only 3 solutions.
- Handle through ISNULL function especially when you have some calculations.
- Use the Case Statement when you left join or complex statements.
example: SELECT CASE WHEN Tbl1.Column1=’Value1’ AND Tbl2.Column2=’Value1’ THEN ‘True’ ELSE ‘False’ END as ColumnA
- Another way is to handle through COALESCE function, this function will return NULL if the argument is NULL.
example: SELECT * FROM Table WHERE Column1= COALESCE(@Column1,Column1) AND Column2=@Column2
In above example Column1 is optional.
Note: Never use this whenever you performing any operation statements like UPDATE, DELETE etc. better to use in the SELECT Statements.