Tag: TRY CATCH

This feature has been sometime in SQL Server 2005 in SQL Server 2000 you normally use @@TRANCOUNT to detect any exception but now in SQL Server 2005 you can use try catch block.

In this snippet, there are 2 INSERT statement and 1 UPDATE statement. I’m expecting when there is any failure (e.g the UPDATE statement fails) then all the preceding INSERT/UPDATE/DELETE within the TRANSACTION block will be canceled

e.g

BEGIN TRY
BEGIN TRANSACTION transABC

INSERT INTO TABLEA (col1,col2,col3) VALUES ('a','b','c')
INSERT INTO TABLEB (col1,col2,col3) VALUES ('a','b','c')

UPDATE TABLEA SET col2='abcde' WHERE col1 = 'a'

COMMIT TRANSACTION transABC
END TRY

BEGIN CATCH
IF @@TRANCOUNT > 0
ROLLBACK TRANSACTION transABC --RollBack in case of Error

-- Raise an error with the details of the exception
DECLARE @ErrMsg nvarchar(4000), @ErrSeverity int
SELECT @ErrMsg = ERROR_MESSAGE(),
@ErrSeverity = ERROR_SEVERITY()

RAISERROR(@ErrMsg, @ErrSeverity, 1)

END CATCH