Conditional WHERE hehp - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Conditional WHERE hehp

Author

Topic

Dargon
Starting Member

26 Posts

Posted - 2008-07-30 : 15:26:51

Is it any way to set conditional WHERE clause? I'll try to explain what I need to do.

I have table Products(ProductID,ProductName, Qty) which defines products in stock.
I also have another table ProductBounds(ProductID, LBound, UBound) which defines upper and lower range of Quantity for each product

Products

ID ProductName Qty

1 Cheese 11.2
2 Beer 100
3 Meat 33
4 Candy -114

(yes, it is possible to have negative inventory )

ProductBounds

ProductID LBound UBound
1 3 20
2 10 66
3 5 14
4 20 200

I know that to get products which are overstocked I need to write something like

Select * from Products p
inner join ProductBounds pb on p.ID=pb.ProductID
where Qty>Ubound

But I have stored procedure with input parameter @mode which tells in what range we need to get products.

Create stored procedure GetProducts
@mode varchar(10)

as

IF (@mode ='negative')
begin
Select * from Products p
inner join ProductBounds pb on p.ID=pb.ProductID
where Qty<0
end

IF(@mode='low')
begin
Select * from Products p
inner join ProductBounds pb on p.ID=pb.ProductID
where Qty<=pb.LBound
end

IF(@mode='OK')
begin
Select * from Products p
inner join ProductBounds pb on p.ID=pb.ProductID
where Qty>pb.LBound and Qty<pb.UBound
end

IF(@mode='high')
begin
Select * from Products p
inner join ProductBounds pb on p.ID=pb.ProductID
where Qty>pb.UBound
end

I hate to repeat almost identical select statements. Is it any way to refactor the SP? I tried to set conditional WHERE clause, but no luck so far. Thanks for help, I am really puzzled.
Does anyone have any thoughts?

Dargon

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2008-07-30 : 15:57:00

[code]
where
(@mode != 'negative' or Qty<0) and
(@mode != 'low' or Qty<=pb.LBound) and
(@mode != 'OK' or (Qty>pb.LBound and Qty<pb.UBound)) and
(@mode != 'high' or Qty>pb.UBound)
[/code]

see:
http://weblogs.sqlteam.com/jeffs/archive/2003/11/14/513.aspx

- Jeff
http://weblogs.sqlteam.com/JeffS

Dargon
Starting Member

26 Posts

Posted - 2008-07-31 : 09:57:19

Oh, thanks! You saved my life. I can not imadgine , how ugly my query would be. I may need a lesson in Boolean logic;)
Dargon

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources