Using WHERE CASE in SP

[jlbantang]

Hi, Im using SP with 2 parameters named @criteria and @value. The @criteria serve as column flag in which column to perform the search eg Supplier and Invoice No and the @value which is the query value.
It seems CASE has the flexibility to meet the requirement but didnt work for me.

<code>
select column1
from myview
where
case when (@criteria='a') then
column1=@value
else
column2=@value
end
</code>

Thanks in advance.

[abhijit]

It looks like you want to create your sql statement dynamically.
Do a search for dynamic SQL and hopefully that will help you.

Also, what database are you using? SQL Server or Oracle?

[jlbantang]

Hi abhijit, that was an option I consider but I found something interesting and its looks very promising. It does work when I test but not sure with the performance though compare to dynamic sql.


select column1
from myview
where
case @criteria
when 'a' then case column1=@value then 1 else 0 end
when 'b' then case column2=@value then 1 else 0 end
end=1


BTW Im SQL 2005 dev.

[szlamany]

That's not a good idea - do this instead

If @criteria='a'
Begin
     select column1 
     from myview 
     where column1=@value 
End
Else
Begin
     If @criteria='b'
     Begin
          select column1 
          from myview 
          where column2=@value 
     End
End

Creating a "super-conditional" based SELECT statement makes no sense. It does not run any faster (probably slower) - is a nightmare to modify later...

[techgnome]

I don't know about that... I've had good success with constructs like this:

where 
  case 
    when @Criteria = 'a' and ((Column1 = @SomeValue) or (Column2 = @SomeValue)) then 1
    else 0
  end = 1

-tg

[szlamany]

I live by the rule that CASE should be avoided in a WHERE clause. Pretty much just to keep the execution plans "more sql-standard".

Look at this simple pair of queries - and the attached execution plan - as you can see the first one does a INDEX SEEK (good) - the second does an INDEX SCAN (not so good). Notice the INDEX SCAN is twice as "costly" as the SEEK. If the fields involved are not in an index it's most likely going to do a TABLE SCAN (really, really not so good).

Select * From Concept_T Where CId between 10 and 20

Select * From Concept_T Where Case When CId between 10 and 20 Then 1 Else 0 End=1

[jlbantang]

Hi szlamany, appreciate the comment. Does it mean if I have compound criteria I need to write it 4 (four) times?

if @criteria1= x
Begin
   If @criteria2=x1
       Begin
       End
   else
       Begin
       End
End
else
   If @criteria2=x1
       Begin
       End
   else
       Begin
       End

[szlamany]

Yes - it would mean you write logic for four spots.

[FunkyDexter]

I've often heard the "No Case in a Where clause" argument and have always accepted it at face value.

Just wondering, though, why not have this entirely handled in the Where clause but using Ands and Ors rather than Cases:-

Select Blah
From BlahTable
Where (@Criteria = 'a' and Column1 = @Value)
OR (@Criteria = 'b' and Column2 = @Value)
OR (@Criteria = 'c' and Column3 = @Value)
etc

That would seem like a more "set based" aproach to me where as ifs and elses feel more "procedural". I've done absolutely zero performance testing around that though.

[szlamany]

@funky - I've often used the method you indicate - just a standard WHERE clause with proper use of OR's to handle the major conditional differences.

Although - when the "very fields" you are going to be using for "evaluation" are "conditional" I can see having separate SELECT's.

I also find myself - for the purposes of large SPROCS returning data for reporting - setting up a TEMP TABLE or TABLE VARIABLE with PRIMARY KEY's to include. I can massage and manipulate that TEMP TABLE several times before arriving at the SINGLE SELECT at the bottom of the SPROC that pulls FROM #TEMPTABLE and JOIN's in all the others.

[FunkyDexter]

Although - when the "very fields" you are going to be using for "evaluation" are "conditional" I can see having separate SELECT's

Ok, I can certainly see that from a readibility and maintenance perspective. Are you aware of any performance implications to either aproach? I just want to make sure I'm not missing a trick.

Personally I use my aproach just because it's valid for just about any query. I don't want to have performance tune every single query I write so I just aim for a consistent aproach and then tune anything that's problematic. But I always want to know if there's a known exception case I can accomodate instead of waiting to fix it after the event.