SQL Server › Stored Procedures & Functions

How would you design a stored procedure to handle a complex business process involving multiple steps, proper error handling, and transaction management together?

Advanced

Answer

You would wrap the multiple steps inside a transaction, use a TRY CATCH block to properly handle any errors that occur, roll back the transaction if something fails partway through, log meaningful error details for troubleshooting, and use THROW to communicate the failure back to the calling application, ensuring the entire process either fully succeeds or leaves no partial changes behind.

CREATE PROCEDURE ProcessOrder @CustomerId INT, @Amount DECIMAL(10,2)
AS
BEGIN
  BEGIN TRY
    BEGIN TRANSACTION;
    INSERT INTO Orders (CustomerId, Amount) VALUES (@CustomerId, @Amount);
    UPDATE Customers SET LastOrderDate = GETDATE() WHERE CustomerId = @CustomerId;
    COMMIT TRANSACTION;
  END TRY
  BEGIN CATCH
    IF XACT_STATE() <> 0 ROLLBACK TRANSACTION;
    THROW;
  END CATCH
END;

Real-world example An order processing procedure safely updates both the orders and customers tables together within a single transaction, automatically rolling back both changes if any part of the process fails unexpectedly.

Common follow-ups: How do you test that this kind of procedure correctly rolls back under different failure scenarios?;What additional logging would be valuable to add to a procedure like this?

Transactions & ACID;Error Handling with TRY CATCH