All 7 Error Handling with TRY CATCH questions
More in Error Handling with TRY CATCH
What error information functions are available inside a CATCH block to help you understand what went wrong? How do you combine a TRY CATCH block with a transaction to ensure the database is properly rolled back if an error occurs partway through? What is the difference between using THROW and RAISERROR to raise a custom error in SQL Server? What types of errors cannot be caught by a TRY CATCH block in SQL Server? How do nested TRY CATCH blocks work, and when would you use them in a complex stored procedure?
Advertisement
SQL Server › Error Handling with TRY CATCH

How would you build a centralized, reusable error logging procedure that captures detailed error information whenever it is called from within a CATCH block?

Advanced

Answer

You create a stored procedure that accepts the standard error information as parameters, or calls the error functions directly since they remain accessible within the scope of the calling CATCH block, and inserts that information into a dedicated error log table, giving you a consistent record of every error across your entire application for later analysis.
CREATE PROCEDURE LogError
AS
BEGIN
  INSERT INTO ErrorLog (ErrorNumber, ErrorMessage, ErrorDate)
  VALUES (ERROR_NUMBER(), ERROR_MESSAGE(), GETDATE());
END;

-- Usage inside a CATCH block
BEGIN CATCH
  EXEC LogError;
END CATCH;
Real-world example A large application calls a shared LogError procedure from every CATCH block across dozens of stored procedures, building a single centralized error log table that makes it much easier to spot recurring problems.

Common follow-ups: What additional information would be useful to capture in an error log table besides the basic error details?;How do you avoid the error logging procedure itself failing and masking the original error?

Stored Procedures & Functions;Transactions & ACID
All 7 Error Handling with TRY CATCH questions