SQL SERVER Transaction Example

Stored Procedure format:

CREATE PROCEDURE yourprocedure asbegin SET NOCOUNT on; BEGIN TRY---------------------start catching exception BEIN TRAN------------------start transaction UPDATE A SET a.names= B.names from T1 as A INNER joins T2 as B on a.id =b.id UPDATE A SET a.names= B.names from T1 as A INNER joins T2 as B on a.test =b.test COMMIT TRAN-------COMMIT TRANSACTION END TRY-----------End catch Exception BEGIN catch------------An exception was arrested IF @ @TRANCOUNT>0---------------judge If there is a transaction BEGIN ROLLBACK TRAN----------Rollback Transaction END EXEC yourlogerrorprocedure-----------log error messages when the stored procedure is executed, customizing END CATCH--------ending exception handling end

Ii. Common functions for capturing errors

1, Error_number () returns the error number.

2, Error_severity () returns the severity level.

3, Error_state () returns the error status number.

4, Error_procedure () returns the name of the stored procedure or trigger where the error occurred.

5, Error_line () returns the line number that caused the error.

6, Error_message () returns the full text of the error message. The text can include values provided by any replaceable parameter, such as length, object name, or time.

SQL SERVER Transaction Example