1. First, from the SQL Server Error, the error handles the error handling some blame the error is 16 but the results are different.
SELECT * from FROM is not there if @@ error <> 0 print 'does not output' Go
Raiserror ('', 16, 3) IF @@ error <> 0 print 'This output is' Go
EXEC ('SELECT * FROM is not on the table') IF @@ error <> 0 print 'This output is' Go
EXEC SP_EXECUTESQL N'SELECT * FROM A table not @@ error <> 0 print 'this output
This allows you to find suspicious SQL via EXEC or SP_EXECUTESQL so that you can capture an error that is abnormally terminated later.
Second, lead the isolate: 1, the generation of isolation affairs
SELECT @@ TRANCOUNT number of active transactions - the current number of active transactions is 0
Begin TRAN
SELECT * FROM No @@ error <> 0 begin print 'did not do this!' IF @@Trancount <> 0 rollback tran end
Commit TRAN
SELECT @@ TRANCOUNT currently the number of active transactions - after execution, you see the current number of active transactions in the current connection is 1, and repeated execution will be accumulated each time, this is very resource.
It should be rolled back to Rollback.
2, use existing means to solve isolate business
Print @@TRANCOUNT Print 'Currently connected active transaction number' - the current number of active transactions is 0
IF @@Trancount <> 0 rollback tran - Write here to let the isolation transaction only to the next time your process is called Begin Tran
SELECT * FROM No @@ error <> 0 begin print 'did not do this!' IF @@Trancount <> 0 rollback tran end
Commit TRAN
--- After the execution, you see the current number of active transactions, but repeated execution will not accumulate print @@ TRANCOUNT Print 'currently connected active
Third, using set XACT_ABORT to control part of the wrong execution process
Create Table Table1 (A INT Check (A> 100)) GO
Set XACT_ABORT On Begin TRAN INSERT TABLE1 VALUES (10) Print 'This is not executed here ================================================================================================================================================== ====================== 'print' 'set XACT_ABORT OFF BEGIN TRAN INSERT TABLE1 VALUES (10) Print' This is executed here 'commit tragend Table Table1
However, SET XACT_ABORT has no effect on compiling errors, and it also generates isolated transactions.
Set XACT_ABORT ON Begin Tran Insert One Notched VALUES (10) Print 'This is not executed' COMMIT TRANGO
Print '' print '============================================= = 'print' '
Set XACT_ABORT OFF BEGIN TRAN INSERT One Notched Values (10) Print 'This is not executed in' COMMIT TRANGO
SELECT @@ TRANCOUNT currently connected active number of active transactions - there are two isolated transactions IF @@Trancount <> 0 rollback tran
For various errors in SQL, and Isolated transactions must pay attention to, be careful in T-SQL programming, beware of isolation, try to avoid waste or isolation resources, Microsoft public announces the next version of SQLserve will have built-in abnormal processing. grammar. At that time, you can have better control via code for unpredictable errors.
