Try..Catch in SQL Server

Level : Intermediate

Mentor: Shailendra Chauhan

Duration : 00:03:00

ERROR_NUMBER()

This SQL Server function returns the error number associated with the last T-SQL statement executed. It's helpful for identifying the specific error code generated in case of an error.

Example

BEGIN TRY
  -- Generate a divide by zero error
  SELECT 1 / 0;
END TRY
BEGIN CATCH
  -- Retrieve and print the error number
  PRINT 'Error Number: ' + CAST(ERROR_NUMBER() AS VARCHAR);
END CATCH

ERROR_SEVERITY()

This function returns the severity level of the last error that occurred, which can be useful for determining the impact of an error.

Example

BEGIN TRY
  -- Attempt to insert a duplicate key
  INSERT INTO Employees (EmployeeID, EmployeeName) VALUES (1, 'John Doe');
END TRY
BEGIN CATCH
  -- Retrieve and print the error severity
  PRINT 'Error Severity: ' + CAST(ERROR_SEVERITY() AS VARCHAR);
END CATCH

ERROR_STATE()

ERROR_STATE() returns the state code of the last error. State codes provide additional information about the error's location or cause.

Example

BEGIN TRY
  -- Attempt to access a non-existent table
  SELECT * FROM NonExistentTable;
END TRY
BEGIN CATCH
  -- Retrieve and print the error state
  PRINT 'Error State: ' + CAST(ERROR_STATE() AS VARCHAR);
END CATCH

ERROR_PROCEDURE()

This function returns the name of the stored procedure or trigger where the error occurred.

Example

CREATE PROCEDURE DivideByZeroProcedure
AS
BEGIN
  -- Generate a divide by zero error
  SELECT 1 / 0;
END;
BEGIN TRY
  EXEC DivideByZeroProcedure;
END TRY
BEGIN CATCH
  -- Retrieve and print the error procedure name
  PRINT 'Error Procedure: ' + ERROR_PROCEDURE();
END CATCH

ERROR_LINE()

ERROR_LINE() returns the line number at which the error occurred.

Example

BEGIN TRY
  -- Attempt to execute an invalid SQL statement
  EXEC InvalidSQLStatement;
END TRY
BEGIN CATCH
  -- Retrieve and print the error line number
  PRINT 'Error Line: ' + CAST(ERROR_LINE() AS VARCHAR);
END CATCH

ERROR_MESSAGE()

This function returns the error message text for the last error that occurred.

Example

BEGIN TRY
  -- Attempt to divide by zero
  SELECT 1 / 0;
END TRY
BEGIN CATCH
  -- Retrieve and print the error message
  PRINT 'Error Message: ' + ERROR_MESSAGE();
END CATCH

Errors Unaffected by a TRY...CATCH Construct

Certain errors, like severe system errors, are not caught by a TRY...CATCH block and can terminate the connection or batch execution.

Example

BEGIN TRY
  -- Attempt to divide by zero (this is caught)
  SELECT 1 / 0;
END TRY
BEGIN CATCH
  -- Handle the error
  PRINT 'Caught an error';
END CATCH
-- Example of an uncatchable error
SELECT 1/0; -- This will terminate the batch

Uncommittable Transactions and XACT_STATE

Some errors can make transactions uncommittable, and you can use XACT_STATE() to determine the transaction state.

Example

BEGIN TRANSACTION;
BEGIN TRY
  -- Attempt to insert a duplicate key
  INSERT INTO Employees (EmployeeID, EmployeeName) VALUES (1, 'John Doe');
END TRY
BEGIN CATCH
  -- Check the transaction state
  IF XACT_STATE() = -1
    ROLLBACK;
  ELSE IF XACT_STATE() = 1
    COMMIT;
  -- Handle the error
  PRINT 'Transaction State: ' + CAST(XACT_STATE() AS VARCHAR);
END CATCH

Using TRY...CATCH

TRY...CATCH blocks are used to handle errors gracefully in SQL Server and provide better error handling and control.

Example

BEGIN TRY
  -- Attempt to update a non-existent table
  UPDATE NonExistentTable SET Column1 = 'Value';
END TRY
BEGIN CATCH
  -- Handle the error
  PRINT 'An error occurred: ' + ERROR_MESSAGE();
END CATCH

Using TRY...CATCH in a transaction

You can use TRY...CATCH within a transaction to handle errors while preserving transaction integrity.

Example

BEGIN TRANSACTION;
BEGIN TRY
  -- Attempt to insert data
  INSERT INTO Orders (OrderID, CustomerID) VALUES (1001, 'CustomerA');
END TRY
BEGIN CATCH
  -- Handle the error
  PRINT 'An error occurred: ' + ERROR_MESSAGE();
  ROLLBACK;
END CATCH;
-- Commit the transaction if no error occurred
COMMIT;

Using TRY...CATCH with XACT_STATE

Combine TRY...CATCH with XACT_STATE to handle errors within a transaction and ensure proper transaction management.

Example

BEGIN TRANSACTION;
BEGIN TRY
  -- Attempt to update a non-existent table
  UPDATE NonExistentTable SET Column1 = 'Value';
END TRY
BEGIN CATCH
  -- Handle the error
  PRINT 'An error occurred: ' + ERROR_MESSAGE();
  IF XACT_STATE() = -1
    ROLLBACK;
  ELSE
    COMMIT;
END CATCH;

Self-paced Membership

Upto 60% OFF

Know More

Still have some questions? Let's discuss.