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?

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;
|improve this answer|||||
  • thanks muchly.. – obrietap Dec 13 '19 at 3:45
1

You can use OR:

WHERE yearCompleted = @YEARCOMPLETED OR yearCompleted IS NULL
|improve this answer|||||
  • 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 '19 at 20:40
  • 1
    I'm sorry but you can't. – McNets Dec 12 '19 at 20:45
  • OK cheers. Thought that may have been the case :) thanks for your input. – obrietap Dec 12 '19 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 '19 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://docs.microsoft.com/en-us/sql/t-sql/statements/set-ansi-nulls-transact-sql?view=sql-server-ver15

|improve this answer|||||

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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