Full access to SQLServer exceptions and isolated transactions!

I. First, let's start with "Error" in SQL Server. The Error levels in SQL are 16, but the results are different. Select * from an external table if @ error0print this does not output goraiserror (,) if @ error0print this output goexec (select * from an external table) if @@

I. First, let's start with "Error" in SQL Server. The Error levels in SQL are 16, but the results are different. Select * from an external table if @ error0 print ', there is no output 'go raiserror) if @ error0 print 'output 'go exec ('select * from an external table') if @@

I. First, let's start with "Error" in SQL Server. The Error levels in SQL are 16, but the results are different.

Select * from an external table
If @ error <> 0
Print 'no output'
Go

Raiserror ('', 16, 3)
If @ error <> 0
Print 'output this output'
Go

Exec ('select * from an external table ')
If @ error <> 0
Print 'output this output'
Go

Exec sp_executesql N 'select * from a non-table'
If @ error <> 0
Print 'output this output'

In this way, you can find that execute suspicious SQL through exec or sp_executesql, so that you can catch the error that is aborted with an exception later.

Ii. Lead to isolated transactions:
1. isolated transactions

Select @ trancount the number of active transactions for the current connection -- the number of active transactions for the current connection is 0

Begin tran

Select * from an external table
If @ error <> 0
Begin
Print 'is not executed here! '
If @ trancount <> 0 rollback tran
End

Commit tran

Select @ trancount the number of active transactions of the current connection -- after execution, you can see that the number of active transactions of the current connection is 1, and the repeated execution will accumulate each time, which is very resource-consuming.

It should be that rollback is not rolled back at all.

2. Use existing methods to solve isolated transactions

Print @ trancount print 'number of active transactions for the current connection' -- the number of active transactions for the current connection is 0

If @ trancount <> 0 rollback tran -- write here to keep the isolated transaction only until your next process is called
Begin tran

Select * from an external table
If @ error <> 0
Begin
Print 'is not executed here! '
If @ trancount <> 0 rollback tran
End

Commit tran

--- After execution, you can see that the number of active transactions currently connected is 1, but repeated execution will not accumulate.
Print @ trancount print 'number of active transactions for the current connection'

3. Use set xact_abort to control the execution process of some errors that violate the constraints.

Create table Table1 (a int check (a> 100 ))
Go

Set xact_abort on
Begin tran
Insert table1 values (10)
Print 'not executed here'
Commit tran
Go

Print ''print '============================================ ============== 'print''

Set xact_abort off
Begin tran
Insert table1 values (10)
Print 'executed here'
Commit tran

Go
Drop table table1

However, set xact_abort does not work for compilation errors, and it also produces isolated transactions.

Set xact_abort on
Begin tran
Insert a table that is not in values (10)
Print 'not executed here'
Commit tran
Go

Print ''print '============================================ ============== 'print''

Set xact_abort off
Begin tran
Insert a table that is not in values (10)
Print 'not executed here'
Commit tran
Go

Select @ trancount the number of active transactions currently connected --- two isolated transactions
If @ trancount <> 0 rollback tran

Note the various SQL errors and isolated transactions in t-SQL programming. Be careful with the trap of isolated transactions and avoid wasting or isolating resources as much as possible, microsoft publicly announced that the next version of SQLServe Yukon will have a built-in Exception Handling syntax. At that time, you can use the code to better control unexpected errors.