1

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?

3 Answers 3

5

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;
0
1

You can use OR:

WHERE yearCompleted = @YEARCOMPLETED OR yearCompleted IS NULL
4
  • Thanks McNets. I was hoping to convert 'yearCompleted IS NULL' to be driven by a SET variable if possible? Something like 'yearCompleted = @NULL'. Where '@NULL' is the equivalent of 'IS NULL'.
    – obrietap
    Dec 12, 2019 at 20:40
  • 1
    I'm sorry but you can't.
    – McNets
    Dec 12, 2019 at 20:45
  • OK cheers. Thought that may have been the case :) thanks for your input.
    – obrietap
    Dec 12, 2019 at 20:48
  • 1
    The query seems to be wrong - it will return all records where yearCompleted IS NULL even @YEARCOMPLETED IS NOT NULL.
    – Akina
    Dec 13, 2019 at 4:47
1

For the sake of completeness, you can SET ANSI_NULLS OFF, which is a session setting. I just mention this since it still exists, and also to be very clear that this setting is deprecated. I.e., according to MS documentation, this setting will (can) be removed in the future.

See https://learn.microsoft.com/en-us/sql/t-sql/statements/set-ansi-nulls-transact-sql?view=sql-server-ver15

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.