Writing Clear Error Messages in Procedures

A procedure says something went wrong, and the operator learns nothing else. Clear error messages turn a failure into a specific place to start.

Clear Error Messages Name the Failed Operation

A message should say what the procedure was trying to do. “Customer load failed” is more useful than “Error occurred.” Add the step when a procedure has several stages, such as source read, validation, target apply, or checkpoint update. Keep the wording short enough to read in a job history pane.

I ask what an operator can do with the message at two in the morning. If it requires opening the procedure to discover which table failed, it needs more context. A clear operation name points to the right log and owner.

Do not turn every internal statement into a new message category. Too many nearly identical codes make alerts noisy. Name the business operation and the failure class, then keep detailed diagnostics in a protected run log.

THROW 50010, 'Customer load validation failed. Review reject rows for this run.', 1;

Include Keys That Help Find the Row

A safe business key or RunId can make the error actionable. Include the value that distinguishes this attempt, not every value from the row. A customer ID, file name, or page key can be enough to locate the detailed record. Use the same identifier in the log and alert.

I avoid printing full payloads into SQL Agent history. Logs can be copied, emailed, and retained longer than expected. Personal data and credentials belong behind controlled access. A short error can point to a protected table without carrying the sensitive content itself.

What would you search first after receiving the alert? Put that key in the message if it is safe. If the source has no stable key, assign a run and source row ID during staging. An error without a position is hard to replay.

DECLARE @RunId bigint = 101;
DECLARE @Message nvarchar(2048) =
    FORMATMESSAGE(N'Customer load failed for run %I64d. Review dbo.LoadReject.', @RunId);
THROW 50011, @Message, 1;

Say What the Operator Can Do

A helpful message can suggest the next check: verify a source file, inspect rejected rows, or retry after a transient connection failure. Do not promise that a retry is safe unless the load is idempotent. The instruction should reflect the actual recovery design.

Use a stable error number for programmatic handling and a plain sentence for people. Clients can classify by number while an operator reads the text. State can distinguish specific locations when the same number has a shared meaning. Document both in the procedure contract.

I test the message with someone who did not write the code. If they ask what “ETL 17” means, the message is serving the author, not the operator. The error should travel well outside the query editor.

Preserve the Original SQL Error

An unexpected SQL error already has a number, line, and procedure. In a CATCH block, bare THROW preserves that information. If you replace it with a generic new error, log the original details first. Otherwise the most useful clue disappears.

A known validation failure can use a custom error number and message. A deadlock, permission failure, or duplicate key error should usually retain its native identity for diagnosis. The caller can still attach a RunId to its own log. Do not flatten every problem into “load failed.”

I inspect ERROR_NUMBER(), ERROR_PROCEDURE(), ERROR_LINE(), and ERROR_MESSAGE() in the log. Keep the transaction state in view. If the transaction is uncommittable, roll it back before writing the durable error row.

BEGIN TRY
    SELECT CONVERT(int, N'invalid');
END TRY
BEGIN CATCH
    SELECT ERROR_NUMBER() AS ErrorNumber,
           ERROR_PROCEDURE() AS ErrorProcedure,
           ERROR_LINE() AS ErrorLine,
           ERROR_MESSAGE() AS ErrorMessage;
    THROW;
END CATCH;

Pair Clear Error Messages With Stable Codes

Assign custom error numbers by category, such as validation, source unavailable, or checkpoint conflict. Do not reuse one number for unrelated problems because a monitoring rule can depend on it. Keep the number list in a small shared document or table.

Messages can change wording for clarity without changing the category. That is why clients should not parse English text to decide whether to retry. A retry decision should use the error type and the operation’s idempotency rule. Text is for diagnosis, not a fragile machine interface.

I review an old procedure’s errors before editing it. Changing RAISERROR severity or replacing it with THROW can change SQL Agent outcome and transaction behavior. A clearer sentence is useful only if the failure contract remains correct.

Test the Failure Path Through the Caller

Run the procedure through the application or SQL Agent step that uses it. Confirm the error number reaches the caller, the job fails when it should, and the transaction does not leave partial business data. A query window test alone can miss client handling.

Force one expected validation error and one unexpected SQL error in a safe test environment. Check the visible message and the protected log. Confirm that the log points to the exact run, and that the proposed next action works. A message that says retry after a nonrepeatable write is dangerous.

I also test success. An error path can leave a transaction open or a connection setting changed for the next operation. The procedure should finish in a known state on both paths.

SELECT @@TRANCOUNT AS OpenTransactions, XACT_STATE() AS TransactionState;

Keep Clear Error Messages Short and Honest

An error message is a doorway to evidence, not the whole incident report. State what failed, identify the attempt, and point to the next check. Do not claim the source is wrong when the failure could be a database permission. Use wording that reflects what the code actually detected.

Avoid vague encouragement such as “please try again later” unless the retry policy and expected condition support it. Operators need a cause or a safe next action. If the system cannot classify the cause, say which step failed and preserve the original error.

Clear error messages reduce time spent guessing. They also expose weak recovery design. If you cannot say what to do after a failure, define the restart rule before polishing the text. The best message comes from an operation whose state is understandable.

Before approving an error message, ask whether a caller can act on it. A good message names the failed business rule without exposing a password, connection string, or personal value. Keep the original error number and context when rethrowing an unexpected database error. A friendly phrase that hides the underlying failure makes support slower and can leave a transaction state unclear.

Related reading on this blog: Scope of ERROR_MESSAGE and How to Read a SQL Server Error Message.

An error message is not a sigh from a procedure, it is a short route to the next safe action.

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