SQL Server Transaction Processing (1)

1. It is best to have only one transaction for a stored procedure; multiple transactions consume both resources and are prone to errors;

2. When creating a transaction and ending a transaction, you 'd better have the following judgment:

Code
IF @ TRANCOUNT = 0
BEGIN -- only if @ TRANCOUNT is 0, we do TRAN in TRAN
BEGIN TRANSACTION
SET @ TransactionIsOurs = 1
END
IF @ TransactionIsOurs = 1
BEGIN
COMMIT TRANSACTION
-- SET @ TransactionIsOurs = 0
END
IF @ TransactionIsOurs = 1 AND @ TRANCOUNT> 0
ROLLBACK TRANSACTION

 

3. If the transaction is very long, you can add several more errors at the appropriate position:

Code
IF @ Error <> 0
BEGIN
Raiserror (50002, 16, 1, n'applycheckedobjectsstage ', n'spapplycheckedobjectsstage', 10, n' checked Agence stage ', @ strerrorparm) with log
Set @ returncode =-1000
Goto exit_label
End

 

Note: @ error must be directly followed by the SQL statement to be judged, and other statements cannot be separated between them, because @ error always gets the latest execution result, so it is best to use try... catch...

Code
Use adventureworks;
Go
Begin transaction;

Begin try
-- Generate a constraint violation error.
Delete from Production. Product
WHERE ProductID = 980;
END TRY
BEGIN CATCH
SELECT
ERROR_NUMBER () AS ErrorNumber,
ERROR_SEVERITY () AS ErrorSeverity,
ERROR_STATE () as ErrorState,
ERROR_PROCEDURE () as ErrorProcedure,
ERROR_LINE () as ErrorLine,
ERROR_MESSAGE () as ErrorMessage;

IF @ TRANCOUNT> 0
Rollback transaction;
End catch;

IF @ TRANCOUNT> 0
Commit transaction;
GO