Sql-server – NULL as a @variable within WHERE clasue

nullsql server

I would like to SET 'IS NULL' as a variable and use it in a WHERE clause.
I will be unioning many tables together that carry the same WHERE clause and would like flexibility to use a variable for my yearCompleted column – see example below-

DECLARE @YEARCOMPLETED
SET @YEARCOMPLETED = NULL

SELECT * FROM TABLE1
WHERE yearCompleted = @YEARCOMPLETED
UNION ALL
SELECT * FROM TABLE2
WHERE yearCompleted = @YEARCOMPLETED

In this instance is it possible to find rows where yearCompleted IS NULL using a variable?

Best Answer

This isn't the most elegant solution, but it'll work to return matching rows if @YEARCOMPLETED is a value, or if it is null. This works decently because it's able to make use of an index on the yearCompleted column, but it basically doubles your code with a near duplicate:

DECLARE @YEARCOMPLETED smallint;
SET @YEARCOMPLETED = NULL;

SELECT * 
FROM TABLE1 
WHERE yearCompleted = @YEARCOMPLETED 
UNION ALL 
SELECT * 
FROM TABLE2 
WHERE yearCompleted = @YEARCOMPLETED
UNION ALL
SELECT * 
FROM TABLE1 
WHERE yearCompleted IS NULL
AND @YEARCOMPLETED IS NULL
UNION ALL 
SELECT * 
FROM TABLE2 
WHERE yearCompleted IS NULL
AND @YEARCOMPLETED IS NULL;

If you don't care about SARGability and using an index on that column, you could do this. It's less duplicated code, but probably with worse performance:

DECLARE @YEARCOMPLETED smallint;
SET @YEARCOMPLETED = NULL;

SELECT * 
FROM TABLE1 
WHERE COALESCE(yearCompleted,9999) = COALESCE(@YEARCOMPLETED,9999)
UNION ALL 
SELECT * 
FROM TABLE2 
WHERE COALESCE(yearCompleted,9999) = COALESCE(@YEARCOMPLETED,9999);

The most elegant solution, to balance reducing duplicated code and good performance, is to use dynamic SQL:

DECLARE @YEARCOMPLETED smallint;
SET @YEARCOMPLETED = NULL;

DECLARE @SQL nvarchar(Max);

SET @SQL = 'SELECT * 
FROM TABLE1
WHERE ';

IF (@YEARCOMPLETED IS NULL)
BEGIN
    SET @SQL = @SQL + ' yearCompleted IS NULL';
END
ELSE
BEGIN
    SET @SQL = @SQL + ' yearCompleted = @YEARCOMPLETED ';
END

SET @SQL = @SQL + ' UNION ALL 
SELECT * 
FROM TABLE1
WHERE ';
IF (@YEARCOMPLETED IS NULL)
BEGIN
    SET @SQL = @SQL + ' yearCompleted IS NULL';
END
ELSE
BEGIN
    SET @SQL = @SQL + ' yearCompleted = @YEARCOMPLETED';
END

EXEC sp_executesql @stmt = @SQL, @params = '@YEARCOMPLETED smallint', @YEARCOMPLETED = @YEARCOMPLETED;
Related Question