Using transactions in Stored procedures

simple syntax for using transactions in a stored procedure

When using transactions in a stored procedure, it is important to use the data to maintain the associated integrity of the data, and it is simple to use the transaction in a SQL Server stored procedure, using an example to illustrate its syntax format:

Code

    Create Procedure  myprocedure

    (   @Param1       nvarchar (10),

       @param2    nvarchar (10)

    )

    As

       Begin

           Set    NOCOUNT on    ;

           Set Xact_abort on;

           Begin  Tran

              Delete from       table1 where name= ' abc ';

              Insert into       table2 values (VALUE1,VALUE2,VALUE3);

           Commit Tran

       End

Note: 1, using stored procedures to execute things, you need to turn on the Xact_abort parameter (the default is OFF), set this parameter to on, indicating that when the transaction is executed, if an error occurs, the transcation is set to uncommittable state, Then all operations will be rolled back after the statement block batch is completed, and if the parameter is set to OFF, the statement will not execute when the transaction is executed, and the other correct operation continues if an error occurs.

2. When set NOCOUNT is on, no count is returned (the count indicates the number of rows affected by the Transact-SQL statement, for example, after a delete operation in SQL Server Query Analyzer, the lower window prompts (3) rows affected). When set NOCOUNT is OFF, the count is returned, and we should add set NOCOUNT on the head of the stored procedure so that when the stored procedure is exited, the set NOCOUNT OFF is added to achieve the purpose of optimizing the stored procedure.

Second, set the SavePoint within the transaction.

A user can set a savepoint or tag within a transaction. A savepoint defines the location where a transaction can be returned if a part of the transaction is conditionally canceled. If you roll back a transaction to a savepoint, you must continue to complete the transaction (using more Transact-SQL statements and COMMIT TRANSACTION statements, if necessary), or you must cancel the transaction completely (by rolling the transaction back to its starting point). To cancel the entire transaction, use the ROLLBACK TRANSACTION transaction_name format. This will undo all the statements and procedures for the transaction. Such as:

Code

Create Procedure  myprocedure

As

    Begin

           Set    NOCOUNT on    ;

           Set Xact_abort on;

           
           Begin   Tran  OK  

              Delete  from  rxqz  where qz=   

           Save   Tran  BCD   --Saves a transaction point named BCD

              Update  sz  set   name= ' Lily s ' where name= ' Li Li '--Modify data

           If  @ @error <>0  --Determine if there is an error in modifying data   

               Begin--If something goes wrong  

                      Rollback   Tran  BCD  --ROLLBACK TRANSACTION to BCD restore point

                   Commit   Tran  OK  --Commit Transaction
      
               End   

           Else  --No errors

               Commit  Tran OK--COMMIT Transaction  

     End

Description: 1, @ @error Determine if there is an error, 0 means there is no error, but for that significant error can not be captured, and @ @error can only be effective in the previous sentence of SQL statement.

third, stored procedures use Try...catch Catch Error

You can use the Try...catch statement to catch an error in a stored procedure, as follows:

Code

Create Procedure  myprocedure

    (   @Param1       nvarchar (10),

       @param2    nvarchar (10)

    )

    As

       Begin

           Set    NOCOUNT on    ;

           Begin  Try

              Delete from       table1 where name= ' abc ';

              Insert into       table2 values (VALUE1,VALUE2,VALUE3);

           End    Try

           Begin  Catch

                  SELECT error_number ()  as ErrorNumber,

                       Error_message ()  as  errormessage;

           End    Catch

    End

Description: 1. There are many functions for capturing errors, as follows:

Error_number () returns the error number.

Error_severity () returns the severity.

Error_state () returns the error status number.

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

Error_line () returns the line number in the routine that caused the error.

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.

2, some errors, such as the table name in the SQL statement input error, this is the database engine cannot resolve the table name, the error occurred in the current Try...catch statement cannot be captured, must be called by the outer layer of the stored procedure to use Try...catch to To capture.

Iv. transactions and Try...catch in stored procedures Federated Use

When a transaction is used in a stored procedure, if there is no Try...catch statement, when set XACT_ABORT on, if an error occurs, all SQL operations are automatically rolled back at the end of the batch statement. When set Xact_abort off, if an error occurs, at the end of the batch statement, the system executes all statements that do not have an error, and the statement that the error occurred will not be executed.

When a transaction is used in a stored procedure, if there is a Try...catch statement block, then when the error is caught, you need to manually perform the rollback operation in the catch statement block, or the system will pass an error message to the client. If set XACT_ABORT on at the beginning of the stored procedure, when an error occurs, the system will place the current transaction in a non-committed state, which will set Xact_state () to 1, at which time only the transaction can be rollback and cannot be committed (commit) Operation, we can determine whether a transaction is in a non-committed state based on the value of Xact_state () in the Catch statement block, and if so, you can do rollback. If set Xact_abort off at the beginning of the stored procedure, then when an error occurs, the system does not speak Xact_state () to 1, then we cannot determine in the CATCH block whether a rollback is required based on the value of the function. But we can judge according to the @ @Trancount global variable, if in the catch block to determine that the @ @Trancount value greater than 0, the representative has uncommitted transactions, since entering the catch statement block, then there are uncommitted transactions, The transaction should be rollback, but this approach may not be accurate in some cases. The recommended method is to determine if the rollback operation is required by Xact_abort on the set and then judging the value of xact_state () in the catch.

Let's take a look at two examples:

A Using Set XACT_ABORT on

Code

Create  proc  myprocedure

As

    Begin

       Set XACT_ABORT on;

       Begin try

           BEGIN Tran

              INSERT into teststu values (' Terry ', ' Boy ', 23);

              INSERT into teststu values (' Mary ', ' Girl ', 21);

           Commit Tran

       End Try

       Begin Catch

           --You can use Xact_state () to determine if there are non-committed transactions, non-committed transactions

           --Indicates an error has occurred inside the transaction. Xact_state () has three kinds of values:-1. The transaction is not submitted;

           --1. Transaction may be submitted; 0. Indicates no transaction, at which time commit or rollback will report an error.

           If Xact_state () =-1

              Rollback Tran;

       End Catch

End

two. using set Xact_abort off

Code

Create  proc  myprocedure

As

    Begin

       Set Xact_abort off;

       Begin try

           BEGIN Tran

              INSERT into teststu values (' Terry ', ' Boy ', 23);

              INSERT into teststu values (' Mary ', ' Girl ', 21);

           Commit Tran

       End Try

       Begin Catch

           --xact_state can not be used here to determine whether there are non-committed transactions

           --Only the @ @Trancount can be used to determine if there are uncommitted transactions and uncommitted transactions may not

           --is a non-committed transaction, so using @ @TranCount >0 after rollback is inaccurate

           If @ @TranCount >0

              Rollback Tran;

       End Catch

End

Also, for @ @Trancount It is necessary to note that the BEGIN TRAN statement adds @ @Trancount plus 1. Rollback Tran decrements the @ @Trancount to 0, except for Rollback Tran Savepoint_name, which does not affect @ @Trancount. Commit Tran or commit work decrements the @ @Trancount by 1.

Using transactions in Stored procedures