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

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
---

Show quoteHide quote

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'
Show quoteHide quote

Try this:
DECLARE @Test INT
SET @test = 1

Select *
from customers
where (@Test = 1 and customerID = 'ALFKI') or
  (@Test <> 1 and customerID <> 'ALFKI' )

Perayu


Show quoteHide quote

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
Show quoteHide quote

On Thu, 8 Sep 2005 13:41:02 -0700, WebBuilder451 wrote:

Show quoteHide quoteHi 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

ordered insert - select is not inserting in order
Delete Without Writing To Transaction Log
clustered index on a identity field
ROWS with null values getting truncated.
Dynamic order by case expression problem