a where case question

I need to change the criteria of the select so that if @.thstype is 1 then
where a =b else a<> b. This is in a stored proc...
given declare @.thsType as INT
set @.thsType = 1
select *
from t1,t2,t2,...
where u.unvid case @.thsType When 1 then = else <> end @.thsUnvID
and u.unvtype = @.thsunvType
and i1.idxhstdate = @.thsDate1
and i2.idxhstdate = @.thsDate2
--
thanks (as always)
some day i''m gona pay this forum back for all the help i''m getting
kesYou were so close :-)
Use Northwind
DECLARE @.Test INT
SET @.test = 1
Select *
from customers where customerID =
(CASE WHEN @.Test = 1 THEN 'ALFKI' ELSE '###' END)
HTH, Jens Suessmeyer
http://www.sqlserver2005.de
--
"WebBuilder451" <WebBuilder451@.discussions.microsoft.com> wrote in message
news:7178EEFF-4304-4871-8138-7F2DF1A2D5BC@.microsoft.com...
>I need to change the criteria of the select so that if @.thstype is 1 then
> where a =b else a<> b. This is in a stored proc...
> given declare @.thsType as INT
> set @.thsType = 1
> select *
> from t1,t2,t2,...
> where u.unvid case @.thsType When 1 then = else <> end @.thsUnvID
> and u.unvtype = @.thsunvType
> and i1.idxhstdate = @.thsDate1
> and i2.idxhstdate = @.thsDate2
> --
> thanks (as always)
> some day i''m gona pay this forum back for all the help i''m getting
> kes|||i'm not sure that will work. I really need something like this
Select *
from customers where
CASE WHEN @.Test = 1 THEN
customerID = 'ALFKI'
ELSE
customerID <> 'ALFKI'
--
thanks (as always)
some day i''m gona pay this forum back for all the help i''m getting
kes
"Jens Sü?meyer" wrote:

> You were so close :-)
> Use Northwind
> DECLARE @.Test INT
> SET @.test = 1
> Select *
> from customers where customerID =
> (CASE WHEN @.Test = 1 THEN 'ALFKI' ELSE '###' END)
> HTH, Jens Suessmeyer
> --
> http://www.sqlserver2005.de
> --
> "WebBuilder451" <WebBuilder451@.discussions.microsoft.com> wrote in message
> news:7178EEFF-4304-4871-8138-7F2DF1A2D5BC@.microsoft.com...
>
>|||Try this:
DECLARE @.Test INT
SET @.test = 1
Select *
from customers
where (@.Test = 1 and customerID = 'ALFKI') or
(@.Test <> 1 and customerID <> 'ALFKI' )
Perayu
"WebBuilder451" <WebBuilder451@.discussions.microsoft.com> wrote in message
news:8CBDCEFA-740D-4DA6-BA4D-18452E54FE1C@.microsoft.com...
> i'm not sure that will work. I really need something like this
> Select *
> from customers where
> CASE WHEN @.Test = 1 THEN
> customerID = 'ALFKI'
> ELSE
> customerID <> 'ALFKI'
> --
> thanks (as always)
> some day i''m gona pay this forum back for all the help i''m getting
> kes
>
> "Jens Smeyer" wrote:
>|||well.........., yes you have an answer and thank you!
However, it turned my mega proc of .5 sec into a 6 second proc.
I can write an if else and have two queries in the proc, but i'd like to
avoide that if i could.
Query below, for what is does it's very fast the condition needs to be in
the joined query at the bottom: (i added the or)
select
s.csistkcsisym,
s.csistksym1,
s.csistkcompany,
case s.csistkExchange when 'OTC' THEN 'Nasdaq' Else s.csistkExchange END as
Exchange,
u.unvName,
isnull(r.unvname, '********') as Sector,
case s.csistkActive when 0 then 'INACTIVE' else 'ACTIVE' END as status,
case h2.stkhstBuySell WHEN '' THEN 'N/A' WHEN 'B' THEN 'Buy' WHEN 'S' then
'Sell' ELSE h2.stkhstBuySell END as PFBuySell,
CASE
WHEN h2.stkhstBuySell = 'B' and h1.stkhstBuySell = 'S' THEN 'gnBK'
WHEN h2.stkhstBuySell = 'S' and h1.stkhstBuySell = 'B' THEN 'rdBK'
ELSE 'wtBK'
END as NEWPFBuySell,
h2.stkhstXO,
CASE
WHEN h2.stkhstXO = 'X' and h1.stkhstxo = 'O' THEN 'gnBK'
WHEN h2.stkhstXO = 'O' and h1.stkhstxo = 'X' THEN 'rdBK'
ELSE 'wtBK'
END as NEWPFXO,
CASE h2.stkhstLine WHEN 'A' THEN 'Above' WHEN 'B' THEN 'Below' ELSE 'N/A'
END as Trend,
CASE
WHEN h2.stkhstLine = 'A' AND h1.stkhstLine = 'B' THEN 'gnBK'
WHEN h2.stkhstLine = 'B' AND h1.stkhstLine = 'A' THEN 'rdBK'
ELSE 'wtBK'
END as NEWPFtrend,
case h2.stkhstRSBS WHEN '' THEN 'N/A' WHEN 'B' THEN 'Buy' WHEN 'S' then
'Sell' ELSE h2.stkhstRSBS END as RSBuySell,
CASE
WHEN h2.stkhstRSBS = 'B' AND h1.stkhstRSBS = 'S' THEN 'gnBK'
WHEN h2.stkhstRSBS = 'S' AND h1.stkhstRSBS = 'B' THEN 'rdBK'
ELSE 'wtBK'
END as NEWRSBuySell,
h2.stkhstRSXO,
CASE
WHEN h2.stkhstRSXO = 'X' AND h1.stkhstRSXO = 'O' THEN 'gnBK'
WHEN h2.stkhstRSXO = 'O' AND h1.stkhstRSXO = 'X' THEN 'rdBK'
ELSE 'wtBK'
END as NEWRSXO,
case When (h2.stkhst10wk - h2.stkhstClose) >= 0 then 'Below' else 'Above'
end as tenBeat,
CASE
WHEN ((h2.stkhst10wk - h2.stkhstClose) >= 0) AND ((h1.stkhst10wk -
h1.stkhstClose) < 0) then 'rdBK'
WHEN ((h1.stkhst10wk - h1.stkhstClose) >= 0) AND ((h2.stkhst10wk -
h2.stkhstClose) < 0) then 'gnBK'
ELSE 'wtBK'
END AS NEWtenBeat,
h2.stkhst10Wk,
h2.stkhstClose,
case
WHEN r.i2idxhstStatus is null
then (dbo.fn_rtnRSStatus(h2.stkhstRSBS,
h2.stkhstRSXO)+dbo.fn_rtnPFStatus(h2.stkhstBuySell, h2.stkhstLine))*2
else ((dbo.fn_rtnBPStatus(r.i2idxhstStatus, r.i2idxhstPosChartPos)*50) +
(dbo.fn_rtnRsRStatus(r.i2idxhstRSBSXO)*25) +
(dbo.fn_rtnBPStatus(r.i2idxhstRSXOStatus, r.i2idxhstRSXOPos)*25) +
(dbo.fn_rtn10Status(r.i2idxhst10Status, r.i2idxhst10ChartPos)*50) +
(dbo.fn_rtnBPStatus(r.i2idxhstStatus, r.i2idxhstPosChartPos)*25) +
(dbo.fn_rtnBPStatus(r.i2idxhstRSXOStatus, r.i2idxhstRSXOPos)*25) )/4+
dbo.fn_rtnRSStatus(h2.stkhstRSBS, h2.stkhstRSXO)
+dbo.fn_rtnPFStatus(h2.stkhstBuySell, h2.stkhstLine)
END as StockRate,
case
WHEN r.i2idxhstStatus is null
then (dbo.fn_rtnRSStatus(h2.stkhstRSBS,
h2.stkhstRSXO)+dbo.fn_rtnPFStatus(h2.stkhstBuySell, h2.stkhstLine))*2
else ((dbo.fn_rtn10Status(r.i2idxhst10Status, r.i2idxhst10ChartPos)*50) +
(dbo.fn_rtnBPStatus(r.i2idxhstStatus, r.i2idxhstPosChartPos)*25) +
(dbo.fn_rtnBPStatus(r.i2idxhstRSXOStatus, r.i2idxhstRSXOPos)*25) )/2+
dbo.fn_rtnRSStatus(h2.stkhstRSBS, h2.stkhstRSXO)
+dbo.fn_rtnPFStatus(h2.stkhstBuySell, h2.stkhstLine)
END as ShortTermRate,
case s.csistkActive when 0 then 'INACTIVE' else 'ACTIVE' END as status
from
csistk s
join unvmem m on m.unvmemCsiId = s.csistkcsisym
join unv u on u.unvID = m.unvmemUnvID
join stkhst h2 on h2.stkhstcsisym = s.csistkcsisym
join stkhst h1 on h1.stkhstcsisym = s.csistkcsisym
left join
(select u.unvname,
u.unvid,
m.unvmemcsiid,
i1.idxhstStatus as i1idxhstStatus,
i2.idxhstStatus as i2idxhstStatus,
i2.idxhstPosChartPos as i2idxhstPosChartPos,
i2.idxhstRSBSXO as i2idxhstRSBSXO,
i2.idxhstRSXOStatus as i2idxhstRSXOStatus,
i2.idxhstRSXOPos as i2idxhstRSXOPos,
i2.idxhst10Status as i2idxhst10Status,
i2.idxhst10ChartPos as i2idxhst10ChartPos
from unv u
join unvmem m on m.unvmemunvid = u.unvid
join idxhst i1 on i1.idxhstidxid = u.unvID
join idxhst i2 on i2.idxhstidxid = u.unvID
where @.thsOne = 1
and u.unvid <> @.thsUnvID
and u.unvtype = @.thsunvType
and i1.idxhstdate = @.thsDate1
and i2.idxhstdate = @.thsDate2
or
@.thsOne<> 1
and u.unvid = @.thsUnvID
and u.unvtype = @.thsunvType
and i1.idxhstdate = @.thsDate1
and i2.idxhstdate = @.thsDate2) r on r.unvmemcsiid = s.csistkcsisym
where u.unvid = @.thsUnvID
and h2.stkhstdate = @.thsDate2
and h1.stkhstdate = @.thsDate1
order by s.csistksym1
--
thanks (as always)
some day i''m gona pay this forum back for all the help i''m getting
kes
"Perayu" wrote:

> Try this:
> DECLARE @.Test INT
> SET @.test = 1
> Select *
> from customers
> where (@.Test = 1 and customerID = 'ALFKI') or
> (@.Test <> 1 and customerID <> 'ALFKI' )
> Perayu
>
> "WebBuilder451" <WebBuilder451@.discussions.microsoft.com> wrote in message
> news:8CBDCEFA-740D-4DA6-BA4D-18452E54FE1C@.microsoft.com...
>
>|||On Thu, 8 Sep 2005 13:41:02 -0700, WebBuilder451 wrote:

>well.........., yes you have an answer and thank you!
>However, it turned my mega proc of .5 sec into a 6 second proc.
>I can write an if else and have two queries in the proc, but i'd like to
>avoide that if i could.
>Query below, for what is does it's very fast the condition needs to be in
>the joined query at the bottom: (i added the or)
(snip)
> where @.thsOne = 1
> and u.unvid <> @.thsUnvID
> and u.unvtype = @.thsunvType
> and i1.idxhstdate = @.thsDate1
> and i2.idxhstdate = @.thsDate2
> or
> @.thsOne<> 1
> and u.unvid = @.thsUnvID
> and u.unvtype = @.thsunvType
> and i1.idxhstdate = @.thsDate1
> and i2.idxhstdate = @.thsDate2) r on r.unvmemcsiid = s.csistkcsisym
Hi WebBuilder451,
While this and/or condition will work, it is not very maintainable and
not very efficient either. Please remember that many people do not know
the precedence of evaluation for and and or by head. Just adding
brackets would make this code easier to understand!
But the code below, while equivalent, also has a better chance of being
able to use indexes:
where u.unvtype = @.thsunvType
and i1.idxhstdate = @.thsDate1
and i2.idxhstdate = @.thsDate2
and ((@.thsOne = 1 and u.unvid <> @.thsUnvID)
or (@.thsOne <> 1 and u.unvid = @.thsUnvID))
) r on r.unvmemcsiid = s.csistkcsisym
If that doesn't solve your speed problem and speed is important for you,
than you'll have to duplicate your stored procedure to make two
versions: one for @.thsOne = 1 and one for @.thsOne <> 1. That will allow
SQL Server to create optimized execution plans for both situations.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)