SQL optional parameter

I’m struggling to set optional parameter in MS SQL 2017. It should do:

1. If parameter is null, then ignore the condition, but apply other conditions
2. If parameter is set, then apply the condition

Code below works only, if the parameter is set, but if the parameter is null, then it shows no results.

DECLARE @ShowBenefitsValidatedAfter Date
--SET @ShowBenefitsValidatedAfter = NULL
SET @ShowBenefitsValidatedAfter = DATEADD(month, -1, GETDATE())


WHERE @ShowBenefitsValidatedAfter < IIF(@ShowBenefitsValidatedAfter IS NULL, @ShowBenefitsValidatedAfter, B.BenefitValidationActualDate)
AND B.Status <> 'Cancelled'

Could you advise me, how to amend the condition, please?

Many thanks!

Answer

Like Gordon showed, logic like this is normally expressed as:

WHERE  (@Parameter IS NULL 
   OR   YourColumn = @Parameter)
  AND ...

The problem with this, however, is that this can lead to poorly cached query plans, as the estimates for when @Parameter has a value of NULL and when it doesn’t will likely be vastly different.

For something as simple as what you have here, OPTION (RECOMPILE) would be the simplest solution:

WHERE (B.BenefitValidationActualDate > @ShowBenefitsValidatedAfter
   OR  @ShowBenefitsValidatedAfter IS NULL)
  AND  B.[Status] != 'Cancelled
OPTION (RECOMPILE);

For more complex queries, I personally suggest using dynamic SQL to build the appropriate WHERE, ensuring you parametrise the sp_executesql statement (don’t go injecting values into the statement…). Then the plans can be cached and used when the query is rerun. The above will force the recreation of the plan each time; which can have performance impacts for large, complex queryes.