WHERE clause with conditions

Question

I want to check a value inside a WHERE clause.

How can I turn this pseudo into real SQL code?

declare @includeReserved int = 0 -- can be 0, 1 or 2

select *
from Tickets
where
(
    -- if @includeReserved = 0:
    ticketIsReserved = 0
    -- else if @includeReserved = 1:
    ticketIsReserved = 0 or ticketIsReserved = 1
    -- else if @includeReserved = 2:
    ticketIsReserved = 1
)

Answer 1

Use and and or:

where (@includeReserved = 0 and ticketIsReserved = 0) or
      (@includeReserved = 1 and ticketIsReserved in (0, 1)) or             
      (@includeReserved = 2 and ticketIsReserved = 1)

You might want to include:

or @includeReserved not in (0, 1, 2)

If you want other values to not filter at all.

Answer 2

Try with case conditions...

declare @includeReserved int = 0 -- can be 0, 1 or 2

select *
from Tickets
where
case when (@includeReserved = 0 and ticketIsReserved = 0) then 1
     when (@includeReserved = 1 and (ticketIsReserved = 0 or ticketIsReserved = 1)) then 1 
     when (@includeReserved = 2 and ticketIsReserved = 1) then  end = 1

Answer 3

Use AND and OR:

select *
from Tickets
where
(
  (@includeReserved = 0 and ticketIsReserved = 0)
  or    
  (@includeReserved = 1 and ticketIsReserved in (0,1))
  or
  (@includeReserved = 2 and ticketIsReserved = 1)
)

Answer 4

It sounds like you want this logic:

select *
from Tickets
where
    (@includeReserved = 0 and ticketIsReserved = 0) or
    (@includeReserved = 1 and ticketIsReserved in (0, 1)) or
    (@includeReserved = 2 and ticketIsReserved = 1);