It may also run very slow on a table with large amounts of records - you may want to try getting the list of ID's to delete and performing a delete based on those rather than a a whole table scan delete....
-----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: 31 May 2005 16:01 To: SQL Subject: Re: Kinda urgent - Please critique my stored proc TGirl - Your code should return zero return code. If it does not, then something unexpected happened. You probably need to pass that along to the next program or user. I would add some error checking like this ... Instead of your COMMIT ... set @returnCode_num = @@error if @returnCode_num = 0 commit transaction else rollback transaction That code means two changes at the top of your code. First, the COMMIT transaction forces your BEGIN into BEGIN transaction. The parser will help there. Second, you need to define @returnCode_num. Something like declare @returnCode_num as int. I do that before the BEGIN statement. I borrowed from the Book On-Line (BOL). hth -brian > I am new at creating stored procs and did some research on google regarding the process. > I found multiple ways of creating one and came up with a simple proc on my own based on > my findings. > > Please critique my stored proc and let me know if I should add or remove anything > > > Thanks > TGirl > > > CREATE OR REPLACE Procedure spDeletePastOrderSavedRecords > AS > > BEGIN > DELETE o.orderid, o.name, o.description > FROM orders o INNER JOIN > products p ON o.productID = p. productID > WHERE MAX(o.oder_date) <= ADD_MONTHS(CURRENT_DATE, -2) > > commit; > END; > / --------------------------------------------- This message was sent using LANline's Webmail System. http://webmail.cyburban.com ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Discover CFTicket - The leading ColdFusion Help Desk and Trouble Ticket application http://www.houseoffusion.com/banners/view.cfm?bannerid=48 Message: http://www.houseoffusion.com/lists.cfm/link=i:6:2295 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/6 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:6 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.6 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
