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

score 3 · Accepted Answer · answered Jul 20 '23 at 10:10

3

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);

answered Jul 20 '23 at 10:10

Thom A

88,727
11
45
75

thanks, this works, looks simple, I am trying to understand how it works :) I am not too good with that part, and I see that it works without the Option Recompile too. That thing about Query plans, is about performance , right? – Ash Jul 20 '23 at 10:15
2

The `RECOMPILE` is there as otherwise the cached query plan will be used after the first run. For a *real* (well indexed) table the clauses where `@cucu` has a value of `'a'` or `'b'` will likely result in a seek, however, when it doesn't you want every row and therefore a scan will be used. Using a seek/scan for the other parameter value would result in poor for performance on a larger table. – Thom A Jul 20 '23 at 10:23

Karnav Modi · Answer 2 · 2023-07-22T05:03:18.057

1

In my perspective, I think you are trying to execute SELECT statement based on the value of variable '@cucu'.

Here is my approach with CASE/WHEN for your problem assuming you are working in SSMS.

DECLARE @cucu varchar(10)
SET @cucu = 'c'
SELECT * FROM #t WHERE (CASE WHEN @cucu = 'a' THEN 0 WHEN @cucu = 'b' THEN 1 ELSE p END) = p

edited Jul 22 '23 at 05:03

answered Jul 20 '23 at 12:33

Karnav Modi

53
5

this is also good, I was imagining something like this initially, but was not able to come up with the good answer. One thing though ... you have an extra CASE you need to remove that – Ash Jul 20 '23 at 13:54
1

This, however, wouldn't be SARGable, so if you have any index, @Ash , they wouldn't be used. – Thom A Jul 20 '23 at 15:20
1

Thank you @Ash for pointing my mistake and also thanks to Thom for explanation of SARGable. – Karnav Modi Jul 22 '23 at 05:14

Conditional WHERE clause based on variable

2 Answers2