Stored Procedure format:
CREATE PROCEDUREyourprocedure asBEGIN SETNOCOUNT on; BEGINTRY---------------------Start catching exceptionsBEINTRAN------------------Start a transaction UPDATEASETA.names=B.names fromT1 asAINNER JOINT2 asB ona.ID=b.idUPDATEASETA.names=B.names fromT1 asAINNER JOINT2 asB onA.test=b.testCOMMIT TRAN -------Commit Transactions ENDTRY-----------End Catch Exception BEGINCatch------------have been arrested for unusual IF @ @TRANCOUNT > 0---------------Judge If there is any business BEGIN ROLLBACK TRAN----------rolling back a transaction END EXECYourlogerrorprocedure-----------Log error messages when the stored procedure is executed, customize ENDCatch--------End Exception HandlingEND
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.
Using transactions in SQL Server stored procedures