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.
| Author |
Topic |
|
Nick
Posting Yak Master
155 Posts |
 Posted - 2001-10-03 : 23:03:41
|
| I just started using Stored Procedures today. Coming from a programming background I can pick up the Syntax rather quickly. I would like to know how optimized my code is though. Attached is a code snippit of one of my stored procedures. Could I please have some input on how it could be improved perforamnce wize? Thanks!---CREATE PROCEDURE spDeleteOrder@orderID IntASBEGIN TRANSACTION DECLARE @itemID nvarchar(50) DECLARE @itemQty int DECLARE @newQty int DECLARE c1 CURSOR FOR SELECT itemID, opQty FROM tblOrders_Products WHERE orderID = @orderID OPEN c1 FETCH NEXT FROM c1 INTO @itemID, @itemQty WHILE @@FETCH_STATUS = 0 BEGIN SELECT @newQty = itemQty FROM tblInventory WHERE itemID=@itemID UPDATE tblInventory SET itemQty=@newQty+@itemQty WHERE itemID=@itemID FETCH NEXT FROM c1 INTO @itemID, @itemQty END CLOSE c1 DEALLOCATE c1 DELETE FROM tblOrders WHERE orderID=@orderID DELETE FROM tblOrders_Products WHERE orderID=@orderID DELETE FROM tblBoxes WHERE orderID=@orderIDCOMMIT TRANSACTIONGO |
|
|
|
|
|
Stored Procedure Optimization