a. constraint violation
USE AdventureWorks
BEGIN TRY
UPDATE HumanResources.Employee
SET Gender = 'X'
WHERE EmployeeID = 1;
END TRY
BEGIN CATCH
SELECT ERROR_NUMBER( ) ErrorNumber,
ERROR_STATE( ) ErrorState,
ERROR_SEVERITY( ) ErrorSeverity,
ERROR_MESSAGE( ) ErrorMessage;
END CATCH
b. selecting from non existing table
USE AdventureWorks;
GO
BEGIN TRY
-- Table does not exist; object name resolution
-- error not caught.
SELECT * FROM NonexistentTable;
END TRY
BEGIN CATCH
SELECT
ERROR_NUMBER() as ErrorNumber,
ERROR_MESSAGE() as ErrorMessage;
END CATCH
c. erroneous sp
-- Verify that the stored procedure does not exist.
IF OBJECT_ID ( N'usp_ExampleProc', N'P' ) IS NOT NULL
DROP PROCEDURE usp_ExampleProc;
GO
-- Create a stored procedure that will cause an
-- object resolution error.
CREATE PROCEDURE usp_ExampleProc
AS
SELECT * FROM NonexistentTable;
GO
BEGIN TRY
EXECUTE usp_ExampleProc
END TRY
BEGIN CATCH
SELECT
ERROR_NUMBER() as ErrorNumber,
ERROR_MESSAGE() as ErrorMessage;
END CATCH;
D. division by zero
USE AdventureWorks;
GO
BEGIN TRY
-- Generate a divide-by-zero error.
SELECT 1/0;
END TRY
BEGIN CATCH
SELECT
ERROR_NUMBER() AS ErrorNumber,
ERROR_SEVERITY() AS ErrorSeverity,
ERROR_STATE() AS ErrorState,
ERROR_PROCEDURE() AS ErrorProcedure,
ERROR_LINE() AS ErrorLine,
ERROR_MESSAGE() AS ErrorMessage;
END CATCH;
GO
d. try catch with transaction
USE AdventureWorks;
GO
BEGIN TRANSACTION;
BEGIN TRY
-- Generate a constraint violation error.
DELETE FROM Production.Product
WHERE ProductID = 980;
END TRY
BEGIN CATCH
SELECT
ERROR_NUMBER() AS ErrorNumber,
ERROR_SEVERITY() AS ErrorSeverity,
ERROR_STATE() as ErrorState,
ERROR_PROCEDURE() as ErrorProcedure,
ERROR_LINE() as ErrorLine,
ERROR_MESSAGE() as ErrorMessage;
IF @@TRANCOUNT > 0
ROLLBACK TRANSACTION;
END CATCH;
IF @@TRANCOUNT > 0
COMMIT TRANSACTION;
GO
e. try catch with xact- state
USE AdventureWorks;
GO
-- Check to see if this stored procedure exists.
IF OBJECT_ID (N'usp_GetErrorInfo', N'P') IS NOT NULL
DROP PROCEDURE usp_GetErrorInfo;
GO
-- Create procedure to retrieve error information.
CREATE PROCEDURE usp_GetErrorInfo
AS
SELECT
ERROR_NUMBER() AS ErrorNumber,
ERROR_SEVERITY() AS ErrorSeverity,
ERROR_STATE() as ErrorState,
ERROR_LINE () as ErrorLine,
ERROR_PROCEDURE() as ErrorProcedure,
ERROR_MESSAGE() as ErrorMessage;
GO
-- SET XACT_ABORT ON will render the transaction uncommittable
-- when the constraint violation occurs.
SET XACT_ABORT ON;
BEGIN TRY
BEGIN TRANSACTION;
-- A foreign key constrain exists on this table. This
-- statement will generate a constraint violation error.
DELETE FROM Production.Product
WHERE ProductID = 980;
-- If the DELETE statement succeeds, commit the transaction.
COMMIT TRANSACTION;
END TRY
BEGIN CATCH
-- Execute error retrieval routine.
EXECUTE usp_GetErrorInfo;
-- Test XACT_STATE:
-- If 1, the transaction is committable.
-- If -1, the transaction is uncommittable and should
-- be rolled back.
-- XACT_STATE = 0 means that there is no transaction and
-- a COMMIT or ROLLBACK would generate an error.
-- Test if the transaction is uncommittable.
IF (XACT_STATE()) = -1
BEGIN
PRINT
N'The transaction is in an uncommittable state.' +
'Rolling back transaction.'
ROLLBACK TRANSACTION;
END;
-- Test if the transaction is committable
IF (XACT_STATE()) = 1
BEGIN
PRINT
N'The transaction is committable.' +
'Committing transaction.'
COMMIT TRANSACTION;
END;
END CATCH;
GO
1. Try catch block can be nested
2. Only one block is allowed per batch
3. Catches all execution errors with severity greater than 10 that do not terminate the database connection
4. A TRY block must be followed immediately by an associated CATCH block. Placing any other statements
between the END TRY and BEGIN CATCH statements generates a syntax error.
5. GOTO statements cannot be used to enter a TRY or CATCH block. GOTO statements can be used to jump to a label within the same TRY or CATCH block or to leave a TRY or CATCH block.
There is no finally block
Tools 
Help
