Grievances While Working With RAISERROR In SQL Server

Use Case

---

I know that in the world of professional error handling, most folks use THROW instead of RAISERROR to do things.

In my world, I use RAISERROR in stored procedures I write to give feedback on the state of various things in case I need to track down a bug or other issue. It’s quite useful to track the state of parameters and variables and counts of things along the way without bringing progress to a dead halt the way that THROW does, and with substitution options that PRINT doesn’t offer without building dynamic SQL that can get rather messy either with a lot of converting or nested REPLACE statements.

Anyway, there are some annoying things about working with RAISERROR that I wish were different. Let’s look at those.

Itty Bitty

---

The first-most annoying thing is that you can’t use the bit datatype directly as a substitution variable.

DECLARE
    @bit bit = 0;

RAISERROR(N'This is our message %d', 0, 1, @bit);
RAISERROR(N'This is our message %i', 0, 1, @bit);
RAISERROR(N'This is our message %o', 0, 1, @bit);
RAISERROR(N'This is our message %s', 0, 1, @bit);
RAISERROR(N'This is our message %u', 0, 1, @bit);
RAISERROR(N'This is our message %x', 0, 1, @bit);
RAISERROR(N'This is our message %X', 0, 1, @bit);

All of those attempts will return an error message like so:

Msg 2748, Level 16, State 1, Line 23

Cannot specify bit data type (parameter 4) as a substitution parameter.

Why? I have no idea. It is arguably the simplest data type. To work around this, you have to declare another variable with a workable data type, like a n/char(1) or tinyint to use instead.

Date Me

---

It’s often helpful to record start and end times for steps in a procedure to figure out which parts are slow, etc. But just like bits, you can’t pass any date or time related data types in.

Without being exhaustive, this fails just like above.

DECLARE
    @date date = GETDATE();

RAISERROR(N'This is our message %s', 0, 1, @date);

With a familiar-feeling error message.

Msg 2748, Level 16, State 1, Line 19

Cannot specify date data type (parameter 4) as a substitution parameter.

The only real option here is to use a string, which is slightly less annoying because at least using CONVERT, you can specify the format of it pretty nicely.

This I sort of understand to some degree, because SQL Server doesn’t store dates the way we present dates.

Bigger Integers

---

This is more of a documentation beef than anything. If one were to look at the table of substitution types, one might assume that one could use something from the list to pass a bigint to RAISERROR.

But nope nyet nein etc. one cannot. Buried a bit further down is this helpful gem:

This one will work. Others will not.

DECLARE
    @bigint bigint = 0;

RAISERROR(N'This is our message %I64d', 0, 1, @bigint);

Forgetful

---

Finally, it would be nice if RAISERROR raised an error to aid forgetful users. Consider these scenarios:

DECLARE
    @bigint bigint = 0;

RAISERROR(N'This is our message %I64d', 0, 1);
RAISERROR(N'This is our message', 0, 1, @bigint);

These both successfully print the following messages:

This is our message (null)
This is our message

It might drive you batty thinking that somehow your parameter/variable got turned NULL somehow due to a logical bug in your code something.

You know when you execute a store procedure and either don’t pass a parameter value in that it expects, or you try to pass one in that doesn’t exist, and you get an error message immediately?

That would be useful here.

I know, I know. User error. But a little help would be nice here.

Thanks for reading!

Going Further

---

If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that, and need to solve database performance problems quickly. You can also get a quick, low cost health check with no phone time required.