Conditional where clause in Select statement

Question

I am using a stored procedure to generate a report based on parameters to SP. I have to join different where conditions depending upon parameters passed.

For ex.

ALTER PROCEDURE [dbo].[sp_Report_InventoryAging] 

@TitleFlag int=0, /*0-All veh, 1-Clear Title, 2-Without Clear Title*/

@CompName varchar(100) = 'ALL COMPANIES',

@CompBranchId varchar(50) = 'ALL',  /*All Offices*/

@StateId varchar(50)='All States'       /*All states*/

Select .... Where TitleFlag=@TitleFlag and

Now I want to specify conditions based on parameters like -

If not 'ALL COMPANIES' then upper(Company)=upper(@CompName)
If not 'ALL OFFICES' then OfficeID=@CompBranchId
If not 'ALL States' then StateID=@StateID

How do I merge all of these conditions within where condition of select statement depending upon parameter value?

Any help is highly appreciated.

Thanks Eric, I already used that but that takes too much time — Irfan Ahmed
– Irfan Ahmed, Commented Jun 15, 2009 at 20:10

tekBlues · Accepted Answer · 2009-06-15 20:09:34Z

13

Do it like this:

where
(upper(Company)=upper(@CompName) or @compName = 'ALL COMPANIES')
and 
(OfficeID=@CompBranchId or @CompBranchId = 'ALL OFFICES')
and
(StateID=@StateID or @StateID = 'ALL States')

answered Jun 15, 2009 at 20:09

tekBlues

5,8031 gold badge31 silver badges32 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Irfan Ahmed Over a year ago

Thanks, but it seems you have not understood the problem. If @compName = 'ALL COMPANIES' then no need to add condition, if @StateID = 'ALL States' then no need to add condition etc.

Talljoe Over a year ago

@Irfan, methinks you have not understood the solution. :) You can't dynamically change the where clause without using dynamic SQL (not recommended). What you can do is make sure that when the default string is passed the usual where clause is bypassed. That's where "OR" comes in. "Either the default was passed in OR the company names match." Try the solution before naysaying it.

Rich.Carpenter Over a year ago

That's precisely what the code suggested above does. If @compName = 'ALL COMPANIES', then that will qualify every row. Otherwise, it will evaluate upper(Company) = upper(@compName). Same for the other two parameter columns.

Irfan Ahmed Over a year ago

Yeah friend, you are correct. I got the solution. I must try the solution before naysaying it. Sorry for that. Thanks again...

Collectives™ on Stack Overflow

Conditional where clause in Select statement

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related