September 21, 2005 at 8:47 am
I want to use a cursor to select all the orders within a session and put these records away an order at a time if an order header or detail fails rollback and process the next order or commit and process the next record. I have come up with the following stored procedure but I am new to using transactions in this manner and wondering if this is a good idea doing it this way. Any help will be greatly appreciated.
CREATE PROCEDURE dbo.sp_PutawayRecords(@SyncSessionGuid as uniqueidentifier) as
DECLARE @intErrorCode int
--Load Address records and comments
EXEC @intErrorCode = sp_LoadAddressFromStaging
--If the address records have been loaded without error process orders
IF @intErrorCode = 0
BEGIN
DECLARE @OrderGuid uniqueidentifier
-- Create cursor for processing orders
DECLARE curOrders CURSOR FOR
SELECT orders_id AS guid
FROM orders
WHERE session_guid = @SyncSessionGuid
--Open Cursor
OPEN curOrders
FETCH NEXT FROM curOrders INTO @OrderGuid
WHILE @@FETCH_STATUS = 0
BEGIN
BEGIN TRAN Orders
--Process header records
EXEC @intErrorCode = sp_LoadOrdersFromStaging @SyncsessionGuid,@OrderGuid
--Process detail records
EXEC @intErrorCode = sp_LoadOrderedItemsFromStaging @SyncsessionGuid,@OrderGuid
--If both are successful Comit else rollback and process any existing orders
IF @intErrorCode = 0
BEGIN
COMMIT TRAN Orders
END
ELSE
BEGIN
ROLLBACK TRAN Orders
END
FETCH NEXT FROM curOrders INTO @OrderGuid
END
-- clean Up
CLOSE curOrders
DEALLOCATE curOrders
END
September 21, 2005 at 9:32 am
Looks like you have the just of it. But I don't know what kind of errors your expecting, I think certain errors will cause the transaction to roll back, and the stored procedure to fail prematurely. Like anything Test it.
To me using a cursor is not a terribly good idea. Have you looked at a non cursor solution, or are you just trying this out to learn?
September 21, 2005 at 9:47 am
In the sp I call if the insert/updates fail for the order header or detail I return 1 and want to rollback that particular order. How could I avoid using a cursor in this case?
September 21, 2005 at 9:52 am
Wrong order>>>
1 - Validate with the application that the order is valid.
2 - (assuming batch operation). Revalidate that everything is fine.
3 - Execute the tasks without error.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply