A CATCH clause is the recovery side of T-SQL TRY..CATCH error handling. SQL Server runs protected statements in TRY; if an error occurs, execution leaves the failing line and enters CATCH. If TRY succeeds, the construct ends without running CATCH. section 7.1
Mistake #26: start a transaction and assume an error will clean it up. A failed insert can leave a mismatched transaction count, making later troubleshooting more opaque. Put error-handling logic in CATCH so failure has an explicit path rather than an abandoned transaction. section 7.1
Listing 7.6 Adding a TRY..CATCH block to InsertOrders
ALTER PROCEDURE dbo.InsertSalesOrder
@SalesOrderNumber NVARCHAR(12),
@SalesOrderDate DATE,
@SalesPersonID INT,
@SalesAreaID INT,
@CustomerID INT,
@SalesOrderDeliveryDueDate DATE,
@SalesOrderDeliveryActualDate DATE,
@CurrierUsedForDelivery NVARCHAR(32),
@ProductID INT,
@Quantity INT
AS
BEGIN
BEGIN TRANSACTION
BEGIN TRY
DECLARE @CustomerPrefix NVARCHAR(12) ;
SET @CustomerPrefix = (
SELECT SUBSTRING(CustomerCompanyName,1,3)
FROM dbo.Customers
WHERE CustomerID = @CustomerID
) ;
INSERT INTO dbo.SalesOrderHeaders
VALUES (
@CustomerPrefix + @SalesOrderNumber
, @SalesOrderDate
, @SalesPersonID
, @SalesAreaID
, @CustomerID
, @SalesOrderDeliveryDueDate
, @SalesOrderDeliveryActualDate
, @CurrierUsedForDelivery
) ;
INSERT INTO dbo.SalesOrderDetails (
ProductID
, Quantity
, SalesOrderNumber
)
VALUES (
@ProductID
, @Quantity
, @CustomerPrefix + @SalesOrderNumber
) ;
COMMIT
END TRY
BEGIN CATCH
ROLLBACK ;
THROW ;
END CATCH
END
Inspect the stored procedure pattern: transaction work is placed inside TRY, leaving CATCH as the place to roll back, log, or report an error. section 7.1
Use CATCH to make failures actionable:
- Roll back a transaction when protected work fails. section 7.1
- Raise a meaningful error with THROW or RAISERROR() rather than exposing only a low-level failure. section 7.1
- Record error details in a custom log when support staff need diagnostic information. section 7.1
- Consider alerting unattended-process failures through an enterprise observability tool, or Database Mail and SQL Server Agent alerts. section 7.7
Duplicate sales order
A duplicate key failure in InsertSalesOrder can be trapped in CATCH and turned into the clearer message “A duplicate sales order has been entered.” This separates a business-relevant report from the database constraint detail. section 7.1
TRY..CATCH is not .NET-style TRY..CATCH..FINALLY: SQL Server has no FINALLY block. Treat CATCH as the explicit failure path, and pair it with deliberate transaction handling; XACT_ABORT also determines whether an entire transaction terminates when a low-severity error causes a statement to fail. section 7.1 section 7.7
