A NULL literal is used in a comparison or expression

This Best Practice rule will warn you if a comparison or expression includes a NULL literal ('NULL').

According to the SQL Standard, the use of WHERE <expression> = NULL, for example, is invalid syntax and should lead to an error message or an exception. Unfortunately, in SQL Server it doesn't: it just always produces a NULL result. Instead, to determine whether a datatype is, or isn't, NULL, use IS NULL or IS NOT NULL.

SQL Prompt can only detect use of a NULL literal. It is doing a static test so cannot detect if you have a NULL value in a variable. Even if it could do so, it wouldn't be a good thing to flag, because then you'd get warnings on code that use these nullable variables in the useful and valuable ways. Generally, if you are working with a nullable datatype in an expression, you need to use the COALESCE() function, or possibly ISNULL() or NULLIF(), to deal with NULL values safely.

For more details, and some examples, see the Product Learning article: The '= NULL' Mistake and other SQL NULL Heresies.

Available in

 SQL Monitor

SQL Prompt

 SQL Code Guard