Error :4127 – At least one of the arguments to COALESCE must be a typed NULL

This error message is shipped with SQL Server 2005 and it occurs due to one of the nice functions namely COALESCE . Lets discus the cause and resolution for this error message.

Let me explain this error in detail :

Message Number: 4127

Severity : 16

Error Message: At least one of the arguments to COALESCE must be a typed NULL

Error Generation:

Let me create a sample to demonstrate this error.

Select COALESCE(NULL,NULL,NULL) --OUTPUT Msg 4127, Level 16, State 1, Line 1 At least one of the arguments to COALESCE must be a typed NULL.

Ooopps…… I am unable to execute it.

Resolution:

The reason behind this error is that COALESCE returns the first nonnull expression among its arguments and if you look at above expression, there is no nonnull expression. Given below are the two solutions to resolve it.

Solution 1 :
You need to convert any one of the arguments as typed datatype.

SELECT COALESCE(NULL,NULL,CONVERT(INT,NULL)) --OUTPUT NULL

Solution 2 :
You need to add an additional argument as ‘N/A’ (meaning not applicable).

SELECT COALESCE(NULL,NULL,NULL,'N/A') --OUTPUT N/A

Conclusion :
Remember, whenever you use COALESCE make sure that at least one argument is nonnull or data typed or add one more variable N/A (‘Not Applicable’) as an argument.