Conditional WHERE clause based on variable

Question

I have something like this:

declare @cucu varchar(10)
select @cucu = 'c'

create table #t(id int, title varchar(50), p bit)
insert into #t(id, title, p) values (1, 'alpha', 0)
insert into #t(id, title, p) values (2, 'beta', 1)
insert into #t(id, title, p) values (3, 'gamma', 0)

if (@cucu = 'a')
begin
    select  *
    from    #t
    where   p = 0
end
else if (@cucu = 'b')
begin
    select  *
    from    #t
    where   p = 1
end
else
begin
    select  *
    from    #t
end

drop table #t

Is there a way to have those 3 cases treated in a CASE/WHEN/END on the WHERE of the select * from #t query somehow?

If I create an other variable, like this:

declare @b bit; select @b = 0; if @cucu = 'b' select @b = 1

then the first 2 cases are simple to have in one query:

select  *
from    #t
where   p = @b

But for the 3rd possibility, I don't know how to do this

Answer 1

You would need to use OR and AND clauses. You'd also likely want to include a RECOMPILE in the OPTION clause, as the query plans for the 3 queries could be quite different (as I assume this would be run against a much larger table):

SELECT id,
       title,
       p
FROM #t
WHERE (p = 0 AND @cucu = 'a')
   OR (p = 1 AND @cucu = 'b')
   OR (@cucu NOT IN ('a','b')) --Assumes @cucu isn't going to have a NULL value
OPTION (RECOMPILE);