Prev Next

Error Handling

Error handling in SQL Server is crucial to manage and respond to runtime errors gracefully. Using the TRY...CATCH construct, you can catch errors, log them, and handle them appropriately to maintain data integrity and provide meaningful feedback.

Using TRY...CATCH for Error Handling

The TRY...CATCH structure allows you to catch errors within a TRY block and handle them in the CATCH block.

Syntax:

BEGIN TRY
    -- SQL statements that might throw an error
END TRY
BEGIN CATCH
    -- Error handling code
END CATCH;

Example: Handling an Error

BEGIN TRY
    -- Attempt to divide by zero
    DECLARE @Result INT;
    SET @Result = 10 / 0;
END TRY
BEGIN CATCH
    -- Handle the error
    PRINT 'An error occurred: ' + ERROR_MESSAGE();
END CATCH;

Output:

An error occurred: Divide by zero error encountered.

Common Functions for Error Handling

  • ERROR_NUMBER(): Returns the error number.
  • ERROR_SEVERITY(): Returns the severity of the error.
  • ERROR_STATE(): Returns the state number of the error.
  • ERROR_PROCEDURE(): Returns the name of the stored procedure or trigger where the error occurred.
  • ERROR_LINE(): Returns the line number where the error occurred.
  • ERROR_MESSAGE(): Returns the full error message.

Example: Logging an Error

BEGIN TRY
    -- Attempt to insert a duplicate primary key value
    INSERT INTO FreedomFighters (FighterID, Name, City, BirthDate)
    VALUES (1, 'Duplicate Entry', 'Delhi', '1900-01-01');
END TRY
BEGIN CATCH
    -- Log the error details
    INSERT INTO ErrorLog (ErrorNumber, ErrorMessage, ErrorProcedure, ErrorLine)
    VALUES (ERROR_NUMBER(), ERROR_MESSAGE(), ERROR_PROCEDURE(), ERROR_LINE());
    PRINT 'An error has been logged.';
END CATCH;

Do's and Don'ts

Do's

  • Use TRY...CATCH to handle runtime errors gracefully.
  • Log errors in a dedicated table for easier debugging and monitoring.
  • Return meaningful error messages to the client or user.

Don'ts

  • Don't assume errors won't occur; always include error handling in your code.
  • Don't expose detailed error messages to end users in a production environment.
  • Don't use GOTO statements for error handling; use structured methods instead.
Prev Next