Error Handling with TRY CATCH
7 questions found
How does a TRY CATCH block work in SQL Server, and why is it useful?
Beginner
A TRY CATCH block lets you run a piece of SQL code inside the TRY section, and if any error occurs during its execution, control immediately jumps to the CATCH section instead of stopping the entire batch abruptly, letting you handle the error gracefully, such as logging it or rolling back a transaction.
BEGIN TRY
INSERT INTO Orders (OrderId, Amount) VALUES (1, 100);
END TRY
BEGIN CATCH
PRINT 'An error occurred: ' + ERROR_MESSAGE();
END CATCH;
Real-world example
An order processing script catches an error that occurs when trying to insert a duplicate order id, printing a clear message instead of letting the entire batch fail with an unhandled, confusing error.
Common follow-ups: What types of errors are not caught by a TRY CATCH block?;How do you retrieve details about the specific error that occurred inside the CATCH block?
Transactions & ACID;Stored Procedures & Functions
What error information functions are available inside a CATCH block to help you understand what went wrong?
Beginner
Inside a CATCH block, you can use functions like ERROR_MESSAGE to get the actual error text, ERROR_NUMBER for the specific error code, ERROR_LINE for the line number where the error occurred, and ERROR_SEVERITY and ERROR_STATE for additional technical details, all of which help you log or respond to the error appropriately.
BEGIN CATCH
SELECT ERROR_NUMBER() AS ErrorNumber,
ERROR_MESSAGE() AS ErrorMessage,
ERROR_LINE() AS ErrorLine;
END CATCH;
Real-world example
A logging system captures the specific error number, message, and line number whenever a stored procedure fails, giving the support team detailed information to quickly diagnose exactly what went wrong.
Common follow-ups: Can these error functions be used outside of a CATCH block?;What is the difference between error severity and error state?
Stored Procedures & Functions;SQL Server Profiler & Extended Events
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?
Intermediate
You begin a transaction inside the TRY block, and if an error occurs and control moves to the CATCH block, you check whether a transaction is still open using XACT_STATE and roll it back if necessary, ensuring no partial, inconsistent changes are left in the database after an error interrupts a multi step operation.
BEGIN TRY
BEGIN TRANSACTION;
UPDATE Accounts SET Balance = Balance - 100 WHERE AccountId = 1;
UPDATE Accounts SET Balance = Balance + 100 WHERE AccountId = 2;
COMMIT TRANSACTION;
END TRY
BEGIN CATCH
IF XACT_STATE() <> 0
ROLLBACK TRANSACTION;
THROW;
END CATCH;
Real-world example
A bank transfer procedure rolls back both account updates if either one fails partway through, guaranteeing the customer's money is never lost or duplicated due to an unexpected error interrupting the transfer.
Common follow-ups: What does XACT_STATE actually return, and why is checking it important?;What is the difference between using THROW and RAISERROR to re-raise an error?
Transactions & ACID;Isolation & Locking
What is the difference between using THROW and RAISERROR to raise a custom error in SQL Server?
Intermediate
THROW is the newer, simpler way to raise an error, automatically preserving the original error information when used without parameters inside a CATCH block, while RAISERROR is the older approach that offers more formatting options like inserting values into the error message, but requires slightly more code to use correctly and does not automatically stop batch execution the same way THROW does.
BEGIN CATCH
THROW; -- re-throws the original error exactly as it occurred
END CATCH;
-- Custom error message
THROW 50001, 'Insufficient balance for this transaction.', 1;
Real-world example
A stored procedure re-throws a caught error using THROW after logging it, preserving all the original error details for the calling application while still recording what happened for troubleshooting purposes.
Common follow-ups: Can THROW be used to raise a brand new custom error, not just re-throw an existing one?;Why might a team choose RAISERROR over THROW in certain situations?
Stored Procedures & Functions;Transactions & ACID
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
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
How do nested TRY CATCH blocks work, and when would you use them in a complex stored procedure?
Advanced
A CATCH block can contain its own nested TRY CATCH block, letting you attempt a recovery action, like retrying an operation or performing partial cleanup, while still safely catching any new error that might occur during that recovery attempt itself, preventing an error inside your error handling code from crashing the entire procedure unexpectedly.
BEGIN TRY
-- main operation
END TRY
BEGIN CATCH
BEGIN TRY
-- attempt cleanup or logging
EXEC LogError;
END TRY
BEGIN CATCH
PRINT 'Logging itself failed: ' + ERROR_MESSAGE();
END CATCH
END CATCH;
Real-world example
A critical financial procedure wraps its error logging call in its own nested TRY CATCH block, ensuring that even if the logging system itself has a problem, the original error handling process does not crash unexpectedly.
Common follow-ups: How deep can TRY CATCH blocks realistically be nested in SQL Server?;What are some real world scenarios where nested error handling like this becomes necessary?
Stored Procedures & Functions;Transactions & ACID
What types of errors cannot be caught by a TRY CATCH block in SQL Server?
Intermediate
TRY CATCH cannot catch certain severe errors that terminate the database connection entirely, such as a fatal error causing the session to disconnect, compile time syntax errors that prevent the batch from even starting, or certain very low severity warnings that do not actually raise a catchable error condition at all.
-- A syntax error prevents the batch from compiling
-- and cannot be caught by TRY CATCH, since it never actually starts running
BEGIN TRY
SELEC * FROM Orders; -- typo causes a compile error
END TRY
BEGIN CATCH
PRINT 'This will never run';
END CATCH;
Real-world example
A developer is confused when a TRY CATCH block does not catch an error, only to realize it was actually a compile time syntax error in their SQL statement, which prevents the code from ever reaching the point where TRY CATCH could intervene.
Common follow-ups: How do you catch a compile time error before it ever reaches production?;What severity level of error causes a connection to be terminated entirely?
Stored Procedures & Functions;Isolation & Locking