Hi

I am writing a SP that takes a number of parameters, two of these are
aItemRef and aEntityRef. Each of these can contain a value or be NULL.

if aitemRef is NULL the SP should return all items otherwise it should
filter to just the selected item. aEntityRef works in a similar way. I
have this working in the following SQL (last two lines)

SELECT co.DelAddress, co.EntityRef, cs.CliStkRef,
co.CEntityRef, cs.ItemRef, co.ClOrderRef,
co.Supply, co.OrderNo, co.FREIGHTINC, co.Quote,
cs.Quantity, co.CliOrderNo, co.ReqDate,
co.OrderDate, co.IfLate, cs.ORegionRef,
cs.UnitAmount, cs.TaxAmount, c.ClientName,
itn.Name, co.TrustRef,
(SELECT Sum(sl2.ShelfQty) FROM StockLevel sl2
WHERE sl2.ItemRef = cs.ItemRef),
(SELECT Sum(sl3.CustOrdQty) FROM StockLevel sl3
WHERE sl3.ItemRef = cs.ItemRef),
(SELECT Sum(sl4.SupOrdrQty) FROM StockLevel sl4
WHERE sl4.ItemRef = cs.ItemRef)
FROM ClientOrders co
JOIN ClientStock cs ON co.ClOrderRef = cs.ClOrderRef
JOIN Client c ON c.EntityRef = co.EntityRef
JOIN ItemName itn ON itn.ItemRef = cs.ItemRef
WHERE co.Completed <> 'T'
AND cs.PSlipRef IS NULL
AND cs.TransRef IS NULL
AND (cs.ActionType IS NULL OR (cs.ActionType <> 'H' AND
cs.ActionType <> 'C' AND cs.ActionType <> 'P'))
AND (:aEntityRef IS NULL OR co.EntityRef = :aEntityRef)
AND (:aItemRef IS NULL OR cs.ItemRef = :aItemRef)


However the performance is poor, it takes a few seconds to return
results. This is replacing an code based system that built the SQL
manually. In that system it added the last lines only if they were
required, as

AND (co.EntityRef = :aEntityRef)
AND (cs.ItemRef = :aItemRef)

This was fast.

I think adding the 'xxxx IS NULL OR' part to the SQL is stopping the use
of Indexes. Is there a better way of doing this?

p.s I cant do this as 3 SQLs in an IF END ELSE type construct because of
the structure of the rest of the SP (I know thats vague, I really just
don't want to because I would have to do it in 4 places so it would be
12 times. Terrible to read, debug and maintain).






--
Rob Martin
Software Engineer

phone +64 03 377 0495
fax +64 03 377 0496
web www.chreos.com

Wild Software Ltd