March 3, 2017 at 8:57 am
I app, code insert data into two tables: MYORDER and MYORDER_HISTORY (They are in the same server with ORDER and ORDER_HISTORY)
After that, a store procedure below copy data from these two my tables, do update and then upload to another two tables.
After testing, if something goes wrong, data won't upload to saver ORDER and ORDER_HISTORY (It is expected) but data in two tables (MYORDER and MYORDER_HISTORY) did not delete.
What is wrong with this store procedure?
ALTER PROCEDURE [dbo].[Uploadorder]
@ORDERID VARCHAR(30)
AS
DECLARE @myerror INT
BEGIN
SELECT *
INTO #ORDER
FROM MYORDER
WHERE ORDERID = @ORDERID
--SOME CODE TO UPDATE #ORDER HERE
SELECT *
INTO #ORDER_HISTORY
FROM MYORDER_HISTORY
WHERE ORDERID = @ORDERID
--SOME CODE TO UPDATE #ORDER_HISTORY HERE
BEGIN TRAN
INSERT ORDER
SELECT *
FROM #ORDER
SET @myerror = @@ERROR
IF @myerror <> 0
BEGIN
GOTO ERROR_HANDLER
END
INSERT ORDER_HISTORY
SELECT *
FROM #ORDER_HISTORY
SET @myerror = @@ERROR
IF @myerror <> 0
BEGIN
GOTO ERROR_HANDLER
END
COMMIT TRAN
ERROR_HANDLER:
IF @myerror <> 0
BEGIN
ROLLBACK TRAN
DELETE FROM MYORDER
WHERE ORDERID = @ORDERID
DELETE FROM MYORDER_HISTORY
WHERE ORDERID = @ORDERID
END
END
March 3, 2017 at 9:08 am
I would recommend that you change your code so that it returns the error message. That might help you to understand why it's not behaving as you expect. Are your DELETE statements intended only to delete the data that was inserted as part of the transaction? If so, the ROLLBACK should handle that.
John
March 3, 2017 at 9:13 am
The reason of using temp tables is for reporters only.
March 3, 2017 at 11:34 am
adonetok - Friday, March 3, 2017 9:13 AMThe reason of using temp tables is for reporters only.
I'm sorry, but this makes no sense.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply