CODEWITHSUNDEEP
sqlsql-servert-sqlif-statement
Vitomir
Vitomir

Reputation: 295

Multiple IF clause within WHERE clause in T-SQL

I have a table #t1 containing three columns, one of them named root_symbol.

I want to select all elements from table #t1 meeting the condition of variable @root.

However, variable @root can have four different values, thus I would like to dynamically select the filter applied by the where statement depending on the declared variable value.

declare @root integer
set @root = 1

select * from #t1
where root_symbol in
case when @root = 1 then ('ES')
        when @root = 2 then ('EW', 'EW1', 'EW2', 'EW3', 'EW4') 
        when @root = 3 then ('E1C', 'E2C', 'E3C', 'E4C', 'E5C') 
        when @root = 4 then ('E1A', 'E2A', 'E3A', 'E4A', 'E5A') 
end

I have seen this done using case when, however not with the value matching condition in.

An alternative would be to create an if statement with four different select statements inside, however also here I am not sure how to handle multi clause if statements in sql.

Any idea how to solve this?

Upvotes: 1

Views: 376

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271231

Don't use case. Use simple comparisons:

where (@root = 1 and root_symbol in ('ES')) or
      (@root = 2 and root_symbol in ('EW', 'EW1', 'EW2', 'EW3', 'EW4')) or
      (@root = 3 and root_symbol in ('E1C', 'E2C', 'E3C', 'E4C', 'E5C')) or 
      (@root = 4 and root_symbol in ('E1A', 'E2A', 'E3A', 'E4A', 'E5A'))

In general, use case expressions in the where clause is a bad idea (there are some exceptions). Because the case forces the order of evaluation of its clauses, it prevents the optimizer from doing anything.

Upvotes: 6

Related Questions