April 18, 2008 at 10:36 am
Replace the select statement in the SP with the following IF block:
IF (@ItemName IS NULL OR @ItemName = '')
AND (@ItemGroup IS NULL OR @ItemGroup = '')
AND (@ItemPrice IS NULL OR @ItemPrice = 0)
SELECT orderid, custname, orderdate, ISNULL(qty, 0) AS qty
FROM Order_Master om
left join (SELECT orderid AS qtyorderid, COUNT(*) AS qty
FROM Order_Details
GROUP BY orderid) Qtys
on om.orderid = Qtys.qtyorderid
WHERE CustName = ISNULL(@CustName, CustName)
AND OrderId = ISNULL(@OrderId, OrderId)
ELSE
SELECT orderid, custname, orderdate, ISNULL(qty, 0) AS qty
FROM Order_Master om
left join (SELECT orderid AS qtyorderid, COUNT(*) AS qty
FROM Order_Details
GROUP BY orderid) Qtys
on om.orderid = Qtys.qtyorderid
WHERE OrderId in (SELECT orderid FROM Order_Details WHERE itemname = ISNULL(@ItemName, ItemName))
AND OrderId IN (SELECT orderid FROM Order_Details WHERE ItemGroup = ISNULL(@ItemGroup, ItemGroup))
AND OrderId IN (SELECT orderid FROM Order_Details WHERE ItemPrice = ISNULL(@ItemPrice, ItemPrice))
AND CustName = ISNULL(@CustName, CustName)
AND OrderId = ISNULL(@OrderId, OrderId)
As to the statement that the indexes would be ignored because of the ISNULL functions on the right hand side of the '=' sign, I have not found that is the case. I have used this set up before and it would use the indexes. Now, if the ISNULL was on the left hand side of the '=' then, you are correct in that it would ignore the indexes.
Dave N
April 18, 2008 at 11:13 am
This code works perfectly.
Thank you very much for the solution...:)
Viewing 2 posts - 16 through 16 (of 16 total)
You must be logged in to reply to this topic. Login to reply