SQL Error Handling (Try, Catch)

Syntax:

BEGIN TRY

Try Statement 1

Try Statement 2

...

Try Statement M

END TRY

BEGIN CATCH

Catch Statement 1

Catch Statement 2

...

Catch Statement N

END CATCH



Example 1 : Simple TRY…CATCH without RAISERROR function

BEGIN TRY

DECLARE @MyInt INT;

– Following statement will create Devide by Zero Error

SET @MyInt = 1/0;

END TRY

BEGIN CATCH

SELECT ‘Divide by zero error encountered.’ ErrorMessage

END CATCH;

GO

ResultSet:

ErrorMessage

Divide by zero error encountered.

Example 2 : Simple TRY…CATCH with RAISERROR function

BEGIN TRY
DECLARE @MyInt INT;
– Following statement will create Devide by Zero Error
SET @MyInt = 1/0;
END TRY
BEGIN CATCH
DECLARE @ErrorMessage NVARCHAR(4000);

SELECT @ErrorMessage = ERROR_MESSAGE();

RAISERROR (@ErrorMessage, 16, 1);

END CATCH;
GO
ResultSet:
Msg 50000, Level 16, State 1, Line 9

Divide by zero error encountered.

Example 3 :

CREATE PROCEDURE DeleteEmployee ( @EmployeeID int )

AS

BEGIN TRANSACTION -- Start the transaction

-- Delete the Employee's phone numbers

DELETE FROM EmployeePhoneNumbers WHERE EmployeeID = @EmployeeID

-- Delete the Employee record

DELETE FROM Employees WHERE EmployeeID = @EmployeeID

-- See if there is an error

IF @@ERROR <> 0

-- There's an error b/c @ERROR is not 0, rollback

ROLLBACK

ELSE

COMMIT -- Success! Commit the transaction