THROW or RAISERROR

A procedure catches a failure, logs it, and then reports success to the caller. The choice between THROW or RAISERROR can decide whether that mistake survives.

Use THROW for New Error Paths

THROW starts an error with a number, message, and state, or rethrows the current error inside a CATCH block. New application code should generally use it. The bare THROW inside CATCH preserves the original error details. That is useful when the caller needs the real failure, not a generic replacement message.

I inspect procedures that catch an exception and then simply PRINT a message. SQL Agent can mark the step successful because the error never leaves the procedure. A clear log entry does not replace an error result. Let the caller know that the work failed.

What does your calling application expect: an exception, a status row, or both? Decide that contract before changing error syntax. A procedure should not turn a failed transaction into an innocent-looking result set.

BEGIN TRY
    SELECT CONVERT(int, N'not-a-number');
END TRY
BEGIN CATCH
    SELECT ERROR_NUMBER() AS ErrorNumber,
           ERROR_MESSAGE() AS ErrorMessage;
    THROW;
END CATCH;

Know What RAISERROR Still Does Compared to THROW

RAISERROR remains in older code and has capabilities that explain its history. It accepts a message ID from sys.messages or a formatted message string. It also lets the caller choose severity and state within permission rules. Those features can be useful in a legacy interface that relies on specific behavior.

THROW does not accept printf-style formatting directly. Build the message first with FORMATMESSAGE when a parameterized text is needed, then pass the result to THROW. A new THROW error number does not need an entry in sys.messages. Keep error numbers stable if clients classify them.

I do not replace every RAISERROR blindly. Review severity, application handling, and transaction behavior first. A syntax change can alter whether a job fails and whether a transaction is rolled back. The maintenance goal is a better contract, not a prettier keyword.

DECLARE @CustomerId int = 42;
DECLARE @Message nvarchar(2048) =
    FORMATMESSAGE(N'Customer %d has no active rate.', @CustomerId);
THROW 50001, @Message, 1;

Treat THROW or RAISERROR Severity as a Contract

A new THROW error has severity 16. Rethrowing an existing error with bare THROW preserves its severity. RAISERROR allows a chosen severity, and low severities behave more like informational messages than exceptions. That difference affects TRY…CATCH and client behavior.

If old code uses RAISERROR with severity 10 to display progress, replacing it with THROW would turn a message into a failure. Keep progress and failure messages separate. For new error paths, use an actual error that the caller must handle. For progress, use logging or a suitable informational message.

I ask which monitoring system reads severity. A job or application can be built around that signal. Change it deliberately and test the downstream alert. Errors are part of the interface, even when nobody wrote them in the API document.

Understand XACT_ABORT and Transactions With THROW or RAISERROR

SET XACT_ABORT ON makes many run-time errors abort a transaction. THROW honors that setting. RAISERROR does not honor it in the same way. A procedure that uses RAISERROR to signal validation failure can leave an open transaction unless it rolls back explicitly.

Use TRY…CATCH and XACT_STATE() to decide what can be done after a failure. If the transaction is uncommittable, roll it back before trying to write a log row in that transaction. A doomed transaction cannot make your error log durable. Keep logging behavior tested.

I rehearse a failure after one write, not only before any write. That shows whether the procedure leaves partial data, a committed result, or an open transaction. The caller should get a clear failure and a known database state.

SET XACT_ABORT ON;
BEGIN TRY
    BEGIN TRANSACTION;
    THROW 50002, 'Validation failed before commit.', 1;
    COMMIT TRANSACTION;
END TRY
BEGIN CATCH
    IF XACT_STATE() <> 0 ROLLBACK TRANSACTION;
    THROW;
END CATCH;

Preserve Context Without Leaking Data

A useful message names the operation and the key that helps find the failed row. Do not include passwords, full personal data, or an entire JSON payload in the error. Put detailed diagnostics in a protected run log, then return a run ID or safe key to the caller.

THROW message text has a length limit, so keep it concise. Percent signs in a literal THROW message need special care because the character is reserved there. FORMATMESSAGE is a clearer choice for constructed messages. Test the exact string your client receives.

I include the error number and state in logs. A message can change wording while a number remains the programmatic category. State can distinguish where the same error number arose. Do not recycle one number for unrelated failures simply because it is convenient.

Do Not Replace the Original Error Needlessly

A CATCH block can add context and then use bare THROW. It preserves the original error number, severity, state, procedure, and line information. Constructing a fresh RAISERROR or THROW message loses some of that original context unless it is separately logged. Choose based on whether the caller needs the original cause or a stable business error.

For an expected validation rule, a new business error number is reasonable. For an unexpected database error, preserving the original is usually more useful. A duplicate key violation should not be relabeled as “unknown problem” unless the procedure can explain it accurately.

I test both an expected validation failure and a genuine SQL error. The application and SQL Agent should see both as failures, but the messages should lead to different fixes. A catch-all message that says “try again” is not a recovery plan.

Test the Caller, Not Just the Procedure

Run the procedure from the application driver or job step that uses it. Different clients display message severity and result sets differently. Confirm that failed work triggers the intended retry or alert, and that successful work still returns the expected result.

Keep a small error contract in the procedure documentation: error numbers, safe message fields, transaction behavior, and whether the caller can retry. An error that follows an uncertain commit needs an idempotency check before retry. Syntax alone cannot solve that boundary.

THROW is the clear default for new failures. RAISERROR still appears in old systems and has formatting and severity behavior worth understanding. Choose THROW or RAISERROR with the transaction and caller in view, then test the failure path end to end.

Related reading on this blog: Convert Old Syntax of RAISEERROR to THROW and Interview Question of the Week #023: Error Handling with TRY…CATCH.

An error statement is not just a message, it is a contract about failure and transaction state.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.