Sign in to track progress and bookmarks.
Professional T-SQL shouldn't just crash. It should handle errors gracefully, roll back transactions, and log the failure. Modern SQL Server uses TRY...CATCH blocks, just like C# or Java.
Put your risky code inside BEGIN TRY. If an error occurs, the execution jumps immediately to BEGIN CATCH, where you can inspect the error using ERROR_MESSAGE() and ERROR_NUMBER().
BEGIN TRY
BEGIN TRANSACTION
-- Dangerous SQL here
COMMIT TRANSACTION
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION;
PRINT ERROR_MESSAGE();
END CATCHSometimes, an error makes a transaction "Uncommittable." You can't commit it, and you can't even write a log entry using the same connection! XACT_STATE() tells you if the transaction is still alive (1), dead (-1), or non-existent (0), allowing you to handle the cleanup safely.
Q: "What is THROW vs RAISERROR?"
Architect Answer: "`RAISERROR` is the old way; it is flexible but buggy. `THROW` is the modern standard (introduced in 2012). Unlike RAISERROR, `THROW` obeys the SET XACT_ABORT ON setting and properly terminates the batch. It also allows you to 're-throw' an error from a CATCH block while preserving the original error line number, which is vital for debugging."
Quizzes linked to this course—pass to earn certificates.
On this page
1. The TRY...CATCH Pattern 2. XACT_STATE() - The Pro Way 4. Interview Mastery