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;