Tutorial by Examples

This will rollback both inserts due to an invalid datetime: BEGIN TRANSACTION BEGIN TRY INSERT INTO dbo.Sale(Price, SaleDate, Quantity) VALUES (5.2, GETDATE(), 1) INSERT INTO dbo.Sale(Price, SaleDate, Quantity) VALUES (5.2, 'not a date', 1) COMMIT TRANSACTION END TRY BEG...

RAISERROR function will generate error in the TRY CATCH block: DECLARE @msg nvarchar(50) = 'Here is a problem!' BEGIN TRY print 'First statement'; RAISERROR(@msg, 11, 1); print 'Second statement'; END TRY BEGIN CATCH print 'Error: ' + ERROR_MESSAGE(); END CATCH RAISERROR ...

RAISERROR with severity (second parameter) less or equal to 10 will not throw exception. BEGIN TRY print 'First statement'; RAISERROR( 'Here is a problem!', 10, 15); print 'Second statement'; END TRY BEGIN CATCH print 'Error: ' + ERROR_MESSAGE(); END CATCH After RAISER...

You can re-throw error that you catch in CATCH block using TRHOW statement: DECLARE @msg nvarchar(50) = 'Here is a problem! Area: ''%s'' Line:''%i''' BEGIN TRY print 'First statement'; RAISERROR(@msg, 11, 1, 'TRY BLOCK', 2); print 'Second statement'; END TRY BEGIN CATCH print...

You can throw exception in try catch block: DECLARE @msg nvarchar(50) = 'Here is a problem!' BEGIN TRY print 'First statement'; THROW 51000, @msg, 15; print 'Second statement'; END TRY BEGIN CATCH print 'Error: ' + ERROR_MESSAGE(); THROW; END CATCH Exception with be ...