If you get error Msg 4151 “The type of the first argument to NULLIF cannot be the NULL constant because the type of the first argument has to be known” in SQL Server, it’s because you’re passing a null value as the first argument to the NULLIF() function.

To fix this error, make sure you do not pass the null constant as the first argument to the function. Or if you do, then convert it to a specific data type.

Example of the Error

Here’s an example of code that produces the error:

SELECT NULLIF(null, 7);

Result:

Msg 4151, Level 16, State 1, Line 1
The type of the first argument to NULLIF cannot be the NULL constant because the type of the first argument has to be known.

In SQL Server, the NULLIF() function’s return value uses the data type of the first expression. This means that the first value must not be the null constant, seeing as the null constant has no known data type.

This error should be quite rare. In most cases, you’ll pass a column name as the first argument, and in SQL Server, columns have a defined data type. In such cases, a null value will actually be accepted, due to the fact that SQL Server knows the data type of the column.

The same could be said for variables. If you pass a variable, you would have had to declare its type, and therefore, you would not get the error.

In any case, if you do get this error, you could try the following solution.

Solution

If you do get this error, you can convert the null constant to a specific data type:

SELECT NULLIF(CAST(null AS int), 7);

Result:

NULL

In this case NULL is returned, because the two arguments are different and NULL is the first argument.

If the first argument is a column, you won’t need to worry about converting its type, because the column already has a data type.

Suppose we have a ProductPrice column:

SELECT ProductPrice
FROM Products;

Result:

+----------------+
| ProductPrice   |
|----------------|
| 25.99          |
| 14.75          |
| 11.99          |
| 0.00           |
| 0.00           |
| NULL           |
| 9.99           |
| 25.99          |
+----------------+

The column contains a null value.

However, we can pass that column to NULLIF() without a causing error 4151:

SELECT NULLIF(ProductPrice, 0) AS Result
FROM Products;

Result:

+----------+
| Result   |
|----------|
| 25.99    |
| 14.75    |
| 11.99    |
| NULL     |
| NULL     |
| NULL     |
| 9.99     |
| 25.99    |
+----------+

We didn’t get an error, because SQL Server already knows the data type of the column.