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