Using multiple parameter values in where statement 1

[Luvsql]

I have a parameter @Quotes which will prompt the user for Yes or No. If @Quotes='Yes' then I need the where statement to only include field OrderType='QUotes' but if @Quotes='No' then it is to include OrderType='Quotes' and OrderType='Orders'

How do you incorporate an If or case syntax within the where statement of a stored procedure, without having to pass the IF first (so there's 2 queries on the data)?

 

[gmmastros]

Something like this:

Where (@Quotes = 'Yes' And OrderType = 'Quotes')
      Or
      (@Quotes = 'No' And OrderType in ('Quotes','Orders'))

Note that this where clause is not sargable, so performance may not be great, but it's worth a try.


-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[PWise]

you might get better performance if you use this

Select ....
from tablename
where OrderType = 'Quotes'
union
Select ....
from tablename
where OrderType = 'Orders'
And @Quotes = 'No'

 

[gmmastros]

Good point PWise, but I think UNION ALL would be faster still.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Luvsql]

I didn't even think about that logic and it works perfectly!