Case statement 'Operator' in Where clause

i have a sp like this
--
@.operator int --(1=smaller,2=bigger)
@.price decimal(18,2)
select id,price
from company
where (price case @.operator when 1 then <@.price when 2 then >@.price end )
how can i accomplish this?
thnxdeclare @.operator int,
@.price decimal(18,2)
select ID, Price
from Company
where (price < @.price and @.operator = 1 )
and (price > @.price and @.operator = 2)
But if you have this in a Store procedure
declare @.operator int,
@.price decimal(18,2)
if @.operator = 1
begin
select ID, Price
from Company
where price < @.price
end
else if @.operator = 2
begin
select ID, Price
from Company
where price > @.price
end
Also with the logic that you have if the price is the same you will
never see it.
So you need to do this if was in procedure
declare @.operator int,
@.price decimal(18,2)
if @.operator = 1
begin
select ID, Price
from Company
where price <= @.price
end
else if @.operator = 2
begin
select ID, Price
from Company
where price >= @.price
end|||On Wed, 25 Jan 2006 13:38:01 -0800, henk wrote:

>i have a sp like this
>--
>@.operator int --(1=smaller,2=bigger)
>@.price decimal(18,2)
>select id,price
>from company
>where (price case @.operator when 1 then <@.price when 2 then >@.price end )
>--
>how can i accomplish this?
>thnx
Hi Henk,
IF @.operator = 1
SELECT id, price
FROM company
WHERE price < @.price
ELSE
SELECT id, price
FROM company
WHERE price > @.price
Hugo Kornelis, SQL Server MVP|||You can use either options as follows
declare @.operator int --(1=smaller,2=bigger)
declare @.price decimal(18,2)
set @.operator = 1 -- 2
set @.price = 10.05
-- Option A
declare @.cmd varchar(8000)
set @.cmd = 'select id, price from company where price '+ case @.operator when
1 then '< @.price' else '> @.price' end
exec (@.cmd)
-- Option B
if @.operator = 1
select id
,price
from company
where price < @.price
if @.operator = 2
select id
,price
from company
where price >@.price
"henk" wrote:

> i have a sp like this
> --
> @.operator int --(1=smaller,2=bigger)
> @.price decimal(18,2)
> select id,price
> from company
> where (price case @.operator when 1 then <@.price when 2 then >@.price end )
> --
> how can i accomplish this?
> thnx
>|||thanks for your reply
i realy would like to do this in one statment without making use of if-else.
the reason behind this is that i have multiple operators for multiple
columns and it realy gets complex with 4 or 5 operators.
is it possible or do i have to use the if-else senario?
"Amiller" wrote:

> declare @.operator int,
> @.price decimal(18,2)
>
> select ID, Price
> from Company
> where (price < @.price and @.operator = 1 )
> and (price > @.price and @.operator = 2)
> But if you have this in a Store procedure
> declare @.operator int,
> @.price decimal(18,2)
>
> if @.operator = 1
> begin
> select ID, Price
> from Company
> where price < @.price
> end
> else if @.operator = 2
> begin
> select ID, Price
> from Company
> where price > @.price
> end
>
> Also with the logic that you have if the price is the same you will
> never see it.
> So you need to do this if was in procedure
> declare @.operator int,
> @.price decimal(18,2)
>
> if @.operator = 1
> begin
> select ID, Price
> from Company
> where price <= @.price
> end
> else if @.operator = 2
> begin
> select ID, Price
> from Company
> where price >= @.price
> end
>|||DECLARE @.operator int
DECLARE @.price decimal (18,2)
SET @.price = 17.99
--To find items that are less than @.price, set @.operator to 1
--To find items that are more than @.price, set @.operator to 2
SELECT id, price
FROM company
WHERE
CASE
WHEN price - @.price > 0 THEN 2
WHEN price - @.price <= 0 THEN 1
END
= @.operator
The following was tested on Northwind
USE Northwind
GO
DECLARE @.operator int
DECLARE @.price money
SET @.operator = 1
SET @.price = 17.95
select productid, unitprice
FROM Products
WHERE
CASE
WHEN UnitPrice > @.price THEN 2
WHEN UnitPrice < @.price THEN 1
END = @.operator
"henk" wrote:

> i have a sp like this
> --
> @.operator int --(1=smaller,2=bigger)
> @.price decimal(18,2)
> select id,price
> from company
> where (price case @.operator when 1 then <@.price when 2 then >@.price end )
> --
> how can i accomplish this?
> thnx
>|||I am not sure if the first statement will work i have never tried
something like that. I would go with the if else and comment all layers
so you know what one does what.|||thank you all for your helpfull replys.
i think i go with the Mark 's solution. that is exactly what i wanted.
thank you once again.
"Mark Williams" wrote:
> DECLARE @.operator int
> DECLARE @.price decimal (18,2)
> SET @.price = 17.99
> --To find items that are less than @.price, set @.operator to 1
> --To find items that are more than @.price, set @.operator to 2
> SELECT id, price
> FROM company
> WHERE
> CASE
> WHEN price - @.price > 0 THEN 2
> WHEN price - @.price <= 0 THEN 1
> END
> = @.operator
>
> The following was tested on Northwind
> USE Northwind
> GO
> DECLARE @.operator int
> DECLARE @.price money
> SET @.operator = 1
> SET @.price = 17.95
> select productid, unitprice
> FROM Products
> WHERE
> CASE
> WHEN UnitPrice > @.price THEN 2
> WHEN UnitPrice < @.price THEN 1
> END = @.operator
> --
>
> "henk" wrote:
>|||Just to clean things up
DECLARE @.operator int
DECLARE @.price decimal (18,2)
SET @.price = 17.99
--To find items that are less than @.price, set @.operator to 1
--To find items that are more than @.price, set @.operator to 2
SELECT id, price
FROM company
WHERE
CASE
WHEN price > @.price THEN 2
WHEN price <= @.price THEN 1
END
= @.operator
Not sure why I did the whole price - @.price > 0 thing when the above is much
cleaner.
"henk" wrote:

> thank you all for your helpfull replys.
> i think i go with the Mark 's solution. that is exactly what i wanted.
> thank you once again.
>|||yeah i got it,
your the man man!!!!:)sql