Stored Procedure Error Handling

The legacy version of this lesson mixed modern TRY...CATCH with older patterns such as @@ERROR and return-code branching. This rewrite standardizes the approach for SQL Server 2022, clarifies when to handle versus rethrow errors, and shows practical templates for transactions, logging, and status reporting.

1. Code volume and complexity: Stored procedures often contain many statements. The more statements you run (and the more complex they are), the more opportunities you have for conversion errors, constraint violations, deadlocks, timeouts, and logic defects.
2. Schema and dependency change: Procedures execute on the server against live objects. Tables, columns, indexes, and permissions can change independently of your procedure code, which can turn a previously working procedure into a failing one.

Errors are normal in real systems. Robust procedures anticipate them and provide predictable outcomes.

In SQL Server 2022, the primary mechanism for procedural error handling is TRY...CATCH. When an error occurs in the TRY block, control transfers to the CATCH block where you can:

• Rollback or compensate (especially when a transaction is involved).
• Log error diagnostics for later analysis.
• Return a status code and/or output parameters.
• Re-throw the error using THROW so the caller can handle it.

Template: TRY...CATCH with optional transaction

CREATE OR ALTER PROCEDURE dbo.YourProcedureName
AS
BEGIN
    SET NOCOUNT ON;

    BEGIN TRY
        -- Optional: if you need atomicity
        BEGIN TRANSACTION;

        -- Your DML / business logic here
        -- INSERT / UPDATE / DELETE / SELECT

        COMMIT TRANSACTION;
        RETURN 0; -- success (convention)
    END TRY
    BEGIN CATCH
        IF XACT_STATE() <> 0
            ROLLBACK TRANSACTION;

        -- Option A: rethrow the original error (preferred for most procedures)
        THROW;

        -- Option B: return a status code if your design requires it
        -- RETURN 1;
    END CATCH
END;

Use THROW; (no arguments) inside CATCH to preserve the original error number, message, and state. If you need to raise a custom error, use THROW 50000, 'Your message', 1; (custom error numbers are typically 50000+).

Older code often used the global variable @@ERROR immediately after a statement to determine whether the last statement failed. While @@ERROR still exists, it is fragile (it must be checked immediately) and it does not scale well for multi-statement blocks. For SQL Server 2022, prefer TRY...CATCH.

If you inherit legacy code that uses @@ERROR, treat it as a migration target, not a best practice.

Handling an error means you detected a failure and responded intentionally. Depending on your business rules, your procedure might:

1. Do nothing (rare; usually only for non-critical steps).
2. Raise (or re-throw) an error so the caller can act.
3. Exit early with a status code and/or output parameters.

The safest default is to re-throw the original error to the calling program after you perform any required cleanup (rollback, resource cleanup, logging). This keeps the error semantics intact and avoids masking the root cause.

-- Calling code
BEGIN TRY
    -- Execute the stored procedure
    EXEC dbo.usp_GetLastName @EmployeeID = 999;
    
    PRINT 'Procedure executed successfully';
END TRY
BEGIN CATCH
    -- Handle the error from the stored procedure
    PRINT 'Error occurred: ' + ERROR_MESSAGE();
    PRINT 'Error Number: ' + CAST(ERROR_NUMBER() AS varchar(10));
    PRINT 'Error Severity: ' + CAST(ERROR_SEVERITY() AS varchar(10));
    
    -- Optionally re-throw to a higher level
    -- THROW;
END CATCH

DECLARE @ReturnCode int;
DECLARE @StatusOutput int;

BEGIN TRY
    EXEC @ReturnCode = dbo.usp_GetLastName 
        @EmployeeID = 123,
        @ReturnStatus = @StatusOutput OUTPUT;
    
    IF @ReturnCode = 0
        PRINT 'Success. Status: ' + CAST(@StatusOutput AS varchar(10));
    ELSE
        PRINT 'Procedure returned non-zero status: ' + CAST(@ReturnCode AS varchar(10));
END TRY
BEGIN CATCH
    -- When THROW executes in the procedure, control transfers here
    -- Output parameters retain their last assigned value
    PRINT 'Error caught. Status was: ' + CAST(@StatusOutput AS varchar(10));
    PRINT 'Error: ' + ERROR_MESSAGE();
    
    -- Handle or re-throw as needed
    THROW;
END CATCH

CREATE OR ALTER PROCEDURE dbo.usp_GetLastName
    @EmployeeID int
AS
BEGIN
    SET NOCOUNT ON;

    BEGIN TRY
        SELECT e.LastName
        FROM dbo.Employees AS e
        WHERE e.EmployeeID = @EmployeeID;

        RETURN 0;
    END TRY
    BEGIN CATCH
        -- Optional: log here (example shown later)
        THROW; -- re-throw original error to the caller
    END CATCH
END;

To exit a stored procedure immediately, use RETURN. A stored procedure can return a single integer status code. SQL Server does not assign meaning to your return codes; your application (or calling procedure) defines the convention. A common convention is 0 for success and non-zero for failure.

RETURN 0;

If you need both: (1) a predictable return code for the caller and (2) the original error for diagnostics, you can log the error and then re-throw it.
Note: When using THROW with output parameters, the calling code must read the output parameter within its own CATCH block. In most cases, simply using THROW without output parameters is cleaner.

CREATE OR ALTER PROCEDURE dbo.usp_GetLastName
    @EmployeeID int,
    @ReturnStatus int OUTPUT
AS
BEGIN
    SET NOCOUNT ON;
    SET @ReturnStatus = 0;

    BEGIN TRY
        SELECT e.LastName
        FROM dbo.Employees AS e
        WHERE e.EmployeeID = @EmployeeID;

        RETURN 0;
    END TRY
    BEGIN CATCH
        SET @ReturnStatus = 1;

        -- Optional: log the error (see the next section)
        -- Then re-throw so the caller sees the original failure
        THROW;
    END CATCH
END;

In the next lesson, you will learn about and practice using output parameters in more depth. Output parameters are often a better fit than return codes when you need multiple values (status + details + computed results).

In production environments, error logging is typically implemented by inserting diagnostics into a table inside the CATCH block. Keep logging lightweight and avoid masking the original error.

-- Example table (one possible design)
-- CREATE TABLE dbo.ProcedureErrorLog
-- (
--     ErrorLogID int IDENTITY(1,1) PRIMARY KEY,
--     ProcedureName sysname NOT NULL,
--     ErrorNumber int NOT NULL,
--     ErrorSeverity int NOT NULL,
--     ErrorState int NOT NULL,
--     ErrorLine int NULL,
--     ErrorMessage nvarchar(4000) NOT NULL,
--     LoggedAt datetime2(0) NOT NULL DEFAULT sysdatetime()
-- );

-- Example insert inside CATCH:
-- INSERT dbo.ProcedureErrorLog
-- (
--     ProcedureName, ErrorNumber, ErrorSeverity, ErrorState,
--     ErrorLine, ErrorMessage
-- )
-- VALUES
-- (
--     OBJECT_NAME(@@PROCID),
--     ERROR_NUMBER(), ERROR_SEVERITY(), ERROR_STATE(),
--     ERROR_LINE(), ERROR_MESSAGE()
-- );