0

I'd like to quickly dump some context into an error message including some boolean flags using the handy-dandy substitution parameters of RAISERROR such as

CREATE PROCEDURE MyProc
(
    @param1 INT = 1,
    @param2 VARCHAR(255) = 'hello world'
) AS BEGIN
    ...
    RAISERROR('Inconsistent state occurred processing @param1=%d @param2=%s', 16, 1, @param1, @param2)
    ...
END

According to the documentation there is no type specification for bit type (boolean), but notes that

These type specifications are based on the ones originally defined for the printf function in the C standard library.

And as this post notes,

... since any integral type shorter than int is promoted to int when passed down to printf()s variadic arguments, you can use %d

But it does not work in SQL, as I get the following error (2748):

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

Am I forced to use a helper variable?

Thanks.

P.S. Using compatibility mode for 2008R2.

4
  • 4
    Can't you just CAST / CONVERT the BIT column/value to INT or TINYINT when calling?
    – SQLDevDBA
    Commented Jun 25, 2018 at 22:23
  • 2
    You can't do any logic in the call to RAISERROR, everything must be a string literal or variable. Converting to a TINYINT/other type implies using a helper variable. Commented Jun 26, 2018 at 21:52
  • 1
    Good call. How about something like this? This uses formatmessage to make the error message itself, then pushes it into the RAISERROR call. DECLARE @ErrorMessage VARCHAR(1000) = FORMATMESSAGE('Error @param1=%d param2=%s', CAST(@param1 AS INT),@param2); RAISERROR(@ErrorMessage,16,1);
    – SQLDevDBA
    Commented Jun 26, 2018 at 22:21
  • 1
    ahhh, noticed you're using Compat for 2008R2. Sorry, I'm out of gas here.
    – SQLDevDBA
    Commented Jun 26, 2018 at 22:22

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.