COALESCE Function

COALESCE is a function where we can pass multiple parameters and get the first not null value.

For example, Sometimes we have multiple units of a product like Unit1,Unit2,Unit3,Unit4 in the same table and any one of them is filled with data. Now we have to pick any one of them which should not be NULL, in this condition we can use COALESCE.

Another example : 

DECLARE @x INT = NULL

DECLARE @y INT = NULL

DECLARE @z INT = 2

DECLARE @a INT = 3

DECLARE @b INT = 4

SELECT COALESCE(@x,@y,@z,@a,@b)

Rusult : 2

Now let us have a look at different scenarios When the first not null parameter's data type is different datatype parameter and this function will give an error : 

DECLARE @x INT = NULL

DECLARE @y INT = NULL

DECLARE @z VARCHAR(20) = 'q'

DECLARE @a INT = 3

DECLARE @b INT = 4

SELECT COALESCE(@x,@y,@z,@a,@b)

Here you'll get an error message : 

Msg 245, Level 16, State 1, Line 9

Conversion failed when converting the varchar value 'q' to data type int.