I have such select:

SELECT 
 * 
FROM 
 Table Tb 
WHERE
 Tb.LastModificationDate BETWEEN @StartDate And @EndDate 
 AND Tb.var1 = @var1
 AND Tb.var2 = @var2

And its possible that @var1 and @var2 can be null (or only one of them) so for now I have 3 selects for each case (real select is more complicated so I am providing example for simplify things) I am try to merge them into one select with CASE but have no luck so far - as cant understand how to include AND clause in case if one of vars are not null. I believe it should be something like that:

 SELECT 
  * 
 FROM 
  Table Tb 
 WHERE
  Tb.LastModificationDate BETWEEN @StartDate And @EndDate 
  (CASE WHEN @var1 IS NOT NULL THEN (AND Tb.var1 = @var1)
        WHEN @var2 IS NOT NULL THEN (AND Tb.var2 = @var2)
  END);

any advice on that?

CodePudding user response：

SELECT 
* 
FROM 
Table Tb 
WHERE
Tb.LastModificationDate BETWEEN @StartDate And @EndDate 
AND ((@var1 IS NOT NULL AND Tb.var1 = @var1) 
OR
    (@var2 IS NOT NULL AND Tb.var2 = @var2));

I would have done this way.

CodePudding user response：

To use the CASE statement, you would need to remove the AND clause from the THEN portion and move it to outside of CASE so that the result is evaluated. This is because CASE can not append the criteria to the WHERE clause (AND).

SELECT * 
FROM Table Tb 
WHERE Tb.LastModificationDate BETWEEN @StartDate And @EndDate 
AND (CASE 
    WHEN @var1 IS NOT NULL THEN Tb.var1 = @var1
    WHEN @var2 IS NOT NULL THEN Tb.var2 = @var2
END);

However, the above approach will be limited to the first "CASE @var* IS NOT NULL" that evaluates as true. So if both @var1 and @var2 are supplied, the CASE will only return Tb.var1 = @var1, and will not compare Tb.var2 = @var2.

Another approach that circumvents the first case limitation, would be to apply the criteria directly to the WHERE clause by using AND (IS NULL OR ...).

This will allow for the criteria to match under the following 4 conditions:

1. Not checked if both @var1 or @var2 are NULL
2. @var1 if supplied (Tb.var1 = @var1)
3. @var2 if supplied (Tb.var2 = @var2)
4. @var1 and @var2 if both are supplied (Tb.var1 = @var1 AND Tb.var2 = @var2)

SELECT * 
FROM Table Tb 
WHERE Tb.LastModificationDate BETWEEN @StartDate And @EndDate 
AND (@var1 IS NULL OR Tb.var1 = @var1)
AND (@var2 IS NULL OR Tb.var2 = @var2);

Working Examples: DB-Fiddle

CodePudding user response：

Check for both @var1 and @var2 if they are NULL or equal to the value of a column:

 WHERE Tb.LastModificationDate BETWEEN @StartDate And @EndDate 
   AND (@var1 IS NULL OR Tb.var1 = @var1)
   AND (@var2 IS NULL OR Tb.var2 = @var2)