Thursday, 26 January 2017

How to Handle Nulls with ISNULL Function in SQL Server

ISNULL Function to Handle Nulls with in SQL Server
The IsNul function in SQL Server is used to replace the Null value with the user defined value.If you directly use the null value in calculations(+,-,*,/...), the result would be always Null. So, to make sure that your calculations or results not impacted, we have to handle the nulls in SQL.
Syntax :
IsNul(Column_Name,UserDefinedValue)

Example :
CREATE TABLE TEST(ID INT,NAME VARCHAR(20),BASE_SAL INT,BONUS INT)

INSERT INTO TEST (ID,NAME,BASE_SAL,BONUS)
VALUES (123,'TPR',25000,10),
  (234,NULL,30000,NULL),
  (345,'SAI',10000,20);

Before Handling Nulls :
SELECT ID,NAME,BASE_SAL,BONUS,

(BASE_SAL+(BONUS/BASE_SAL)*100) AS NET_SAL FROM Test

Result:


After Handling Nulls :
SELECT ID,ISNULL(NAME,'Not Available') NAME,BASE_SAL,ISNULL(BONUS,0) BONUS,
(BASE_SAL+(ISNULL(BONUS,0)/BASE_SAL)*100) AS NET_SAL FROM Test

Result:


Conclusion :
If you directly use the null value in calculations(+,-,*,/...), the result would be always Null. So, to make sure that your calculations or results not impacted, we have to handle the Nulls in SQL.

Thanks,
TAMATAM


No comments:

Post a Comment

Follow Me by Email

ExcelKingdom-Popular Posts

ExcelKingdom-Random Posts