Skip a WHERE condition if a @variable is empty-CodePudding

In the below code I don't want the second part of the join condition to be applicable if @selectAll = 1 (ie and (listMap.dateOfTask between @startDate and @endDate) I am using SQL Server.

@userId nvarchar(200),
@startDate nvarchar(200),
@endDate nvarchar(200),
@selectAll bit

select 
    *
from tmListMap l
    right join tmTime t
    on 
        l.timeId = t.timeId 
        and (l.dateOfTask between @startDate and @endDate) //Don’t need this condition if @selectAll = 1

CodePudding user response：

@Damien suggested you need a or condition like

@userId nvarchar(200),
@startDate nvarchar(200),
@endDate nvarchar(200),
@selectAll bit

select 
*
from tmListMap l
right join tmTime t
on 
    l.timeId = t.timeId 
    and ((l.dateOfTask between @startDate and @endDate) or @selectAll=1)

CodePudding user response：

@userId nvarchar(200),
@startDate nvarchar(200),
@endDate nvarchar(200),
@selectAll bit

select 
    *
from tmListMap l
    right join tmTime t
    on 
        l.timeId = t.timeId 
where @selectAll = 1 or (l.dateOfTask between @startDate and @endDate)

CodePudding user response：

Two options.

Option A as OR clause

@startDate date,
@endDate date,
@selectAll bit

select 
  *
from tmListMap l
  right join tmTime t
    on  l.timeId = t.timeId 
WHERE
    (
        l.dateOfTask between @startDate and @endDate and @selectAll = 0
      OR
        @selectAll = 1
    )

Option B as UNION ALL

@startDate date,
@endDate date,
@selectAll bit

select 
  *
from tmListMap l
  right join tmTime t
    on  l.timeId = t.timeId 
WHERE @selectAll = 0
    and l.dateOfTask between @startDate and @endDate 
UNION ALL
select 
  *
from tmListMap l
  right join tmTime t
    on  l.timeId = t.timeId 
WHERE @selectAll = 1

Depends a bit on your data which will be faster. In my experience the UNION ALL scales better. As long as the different parts are absolutely not able to create duplicates.