Using transactions in SQL Server stored procedures

Today, a colleague wrote the code, the discovery method directly executes two SQL statements, one is to delete the user, one is to delete the user's permissions. Due to the fact that the database data is more, sometimes this two SQL can not be successfully executed, the database has dirty data.

For this reason, I put two SQL into a stored procedure to execute, adding a transaction to the stored procedure so that it is either executed or not executed.

The code framework is as follows:

1 CREATE PROCEDUREPro_trancdemo@backvalue INTOUTPUT2  as3     BEGIN4         SETNOCOUNT on;5         6         BEGINTRY7             SET @backvalue = 1;--returns the value if the transaction did not roll back, on behalf of the transaction execution success. Note: This sentence can also be placed on the first line after the begin TRAN T, because a rollback in a transaction does not roll back the assignment operation to the variable8             BEGIN TRANT--Start a transaction9             --DELETE statement 1Ten             --DELETE Statement 2 One             --Other SQL sentences ...... A             COMMIT TRANT -         ENDTRY -         BEGINCATCH the             IFXact_state ()<> 0 -                 BEGIN  -                     SET @backvalue = 0;--returns 0 if the transaction is rolled back, representing the transaction execution failure. -                     ROLLBACK TRANT; +                 END -         ENDCATCH +     END  A GO

Comments:

The Xact_state () function is available in SQL Server 2005 and later, and the function returns the following values:

0: The current request does not have an active user transaction

1: The current request has an active user transaction. Requests can perform any action, including writing data and committing transactions

-1: The current request has an active user transaction, but the method commits the transaction or rolls back to the savepoint; it can only request a full rollback of the transaction

My colleague gave me another way to solve this problem, you can refer to:

Using transactions in SQL Server stored procedures