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
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Find out how CFTicket can increase your company's customer support
efficiency by 100%
http://www.houseoffusion.com/banners/view.cfm?bannerid=49
Message: http://www.houseoffusion.com/lists.cfm/link=i:6:2294
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=11502.10531.6
Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
