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."