Transaction rollback

zhaojz1862

I want to call n individual stores in the stored procedure. If one store is unsuccessful, I have to roll back. How do I change it?

CREATE PROCEDURE OnlineHanding
   @UserName VARCHAR (50),
   @ContractID UNIQUEIDENTIFIER
AS

BEGIN TRANSACTION

DECLARE @ErrMessage BIT
  
----- The first step is to deal with non-conforming data
EXEC OnlineCheckData @UserName

------ Update MST_AirlineId and FraeGoupID, RoutingI, and check and insert Routing and insert related tables AirPortGroup, AirPortElement
EXEC OnlineUpdateData @UserName, @ContractID, @ErrMessage OUTPUT

－Other operations ......

...........

IF @@ ERROR = 0
 COMMIT TRANSACTION
ELSE

 ROLLBACK TRANSACTION
--This delete must be done
DELETE OnlineTempTable WHERE Operator = @UserName
GO

zhaojz1862

Whether to return a value in each stored procedure to be called, and then judge whether to rollback based on the returned value

zhaojz1862

If an error occurs in a storage, the statement behind this storage must be aborted. How do I write it?

CREATE PROCEDURE OnlineUpdateData
@UserName VARCHAR (50),
@ContractID UNIQUEIDENTIFIER,
@ErrMessage BIT OUTPUT,
@RollBackFalg INT OUTPUT
 AS

----- Update MST_AirlineId and FraeGoupID
UPDATE OnlineTempTable
SET MST_AirlineId = dbo.GetAirlineIDByAirCode (OAIR), FraeGoupID = dbo.GetFBGroupID (FB, @ ContractID)
WHERE Operator = @UserName --AND Error <> 'N'

IF @@ ERROR <> 0 SET @ RollBackFalg = 1
--- Return when there is an error in this statement, how to write it, I just set RollBackFalg = 1, then return to the storage that calls this storage, and then rollback according to RollBackFalg


INSERT INTO .......

Go

Thank you!

lay1307

Yes, you should judge from the return value of the stored procedure being called, and make the transaction processing decision.

zhaojz1862

Any other suggestions?
Waiting, thank you!

sirenyun

@@error is a global function used to judge the success of the latest SQL statement. It is not recommended that the host use this thing to judge whether the stored procedure is executed successfully. Instead, you should use this function to return different values ​​in each stored procedure. Used to mark whether your stored procedure is executed successfully. Use return to return the result of a stored procedure, just like a C language function. It is enough to judge whether the execution is successful according to the return value of return when calling the stored procedure.

王子静星

Use TRY CATCH. There is a problem with any of the stored procedures inside. If you want it to throw an exception, once the outside sp catches any exception
Just rollback