Suggestion on Stored Procedure

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Suggestion on Stored Procedure

Author

Topic

new_bees
Starting Member

27 Posts

Posted - 2008-05-14 : 18:33:36

Hello,

Can someone on here take a look at my SP and can give some suggestion.
How can I shorten and optimize this code for better performance?

Can someone help me rewrite this code?

This is the only way I can think of. Though it work, but it loads little bit slower.

Here is the code:

if (@Sort = 1)

begin
SELECT MAX(a.ITEMNMBR) AS ITEMNMBR,
a.ITEMDESC AS Description,
ISNULL(SUM(b.Received),0) AS Received,
ISNULL(SUM(c.Sold),0) AS Sold
FROM IV00101 a
LEFT JOIN
(
SELECT ITEMNMBR, SUM(QTYSHPPD) AS Received
FROM POP10500
WHERE (DATEPART([year], DATERECD) =@Year)
AND (DATEPART([month], DATERECD) = @Month)
GROUP BY ITEMNMBR
) b
ON a.ITEMNMBR = b.ITEMNMBR
LEFT JOIN
(
SELECT ITEMNMBR, SUM(QTYORDER) AS Sold
FROM SOP30300
WHERE (DATEPART([year], ACTLSHIP) = @Year)
AND (DATEPART([month], ACTLSHIP) = @Month)
GROUP BY ITEMNMBR
) c
ON a.ITEMNMBR = c.ITEMNMBR
GROUP BY a.ITEMDESC
HAVING SUM(c.Sold) > 0
ORDER BY a.ITEMDESC
end

else if (@Sort = 2)

begin

if (@OrderBy <> '')

begin

SELECT MAX(a.ITEMNMBR) AS ITEMNMBR,
a.ITEMDESC AS Description,
ISNULL(SUM(b.Received),0) AS Received,
ISNULL(SUM(c.Sold),0) AS Sold
FROM IV00101 a
LEFT JOIN
(
SELECT ITEMNMBR, SUM(QTYSHPPD) AS Received
FROM POP10500
WHERE (DATEPART([year], DATERECD) =@Year)
AND (DATEPART([month], DATERECD) = @Month)
GROUP BY ITEMNMBR
) b
ON a.ITEMNMBR = b.ITEMNMBR
LEFT JOIN
(
SELECT ITEMNMBR, SUM(QTYORDER) AS Sold
FROM SOP30300
WHERE (DATEPART([year], ACTLSHIP) = @Year)
AND (DATEPART([month], ACTLSHIP) = @Month)
GROUP BY ITEMNMBR
) c
ON a.ITEMNMBR = c.ITEMNMBR
GROUP BY a.ITEMDESC
HAVING SUM(b.Received) > 0 AND SUM(c.Sold) Is Null
ORDER BY
CASE WHEN @OrderBy = '1' THEN a.ITEMDESC END,
CASE WHEN @OrderBy = '2' THEN SUM(b.Received) END DESC,
CASE WHEN @OrderBy = '3' THEN SUM(b.Received) END

end

end

else if (@Sort = 3)

begin

if (@OrderBy <> '')

begin

SELECT MAX(a.ITEMNMBR) AS ITEMNMBR,
a.ITEMDESC AS Description,
ISNULL(SUM(b.Received),0) AS Received,
ISNULL(SUM(c.Sold),0) AS Sold
FROM IV00101 a
LEFT JOIN
(
SELECT ITEMNMBR, SUM(QTYSHPPD) AS Received
FROM POP10500
WHERE (DATEPART([year], DATERECD) = @Year)
AND (DATEPART([month], DATERECD) = @Month)
GROUP BY ITEMNMBR
) b
ON a.ITEMNMBR = b.ITEMNMBR
LEFT JOIN
(
SELECT ITEMNMBR, SUM(QTYORDER) AS Sold
FROM SOP30300
WHERE (DATEPART([year], ACTLSHIP) = @Year)
AND (DATEPART([month], ACTLSHIP) = @Month)
GROUP BY ITEMNMBR
) c
ON a.ITEMNMBR = c.ITEMNMBR
GROUP BY a.ITEMDESC
HAVING SUM(c.Sold) > 0 AND SUM(b.Received) Is Null
ORDER BY
CASE WHEN @OrderBy = '1' THEN a.ITEMDESC END,
CASE WHEN @OrderBy = '2' THEN SUM(c.Sold) END DESC,
CASE WHEN @OrderBy = '3' THEN SUM(c.Sold) END

end

end

Any suggestions from the Expert?

Thanks,

New_Bee

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-05-14 : 18:36:27

If your queries are the same except for the ORDER BY, then they can be combined. Check out the comments of the "Dynamic ORDER BY" article:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=5942

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Database maintenance routines:
http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspx

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources