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.

  • 3
    Can't you just CAST / CONVERT the BIT column/value to INT or TINYINT when calling? – MguerraTorres Jun 25 '18 at 22:23
  • 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. – Elaskanator Jun 26 '18 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); – MguerraTorres Jun 26 '18 at 22:21
  • 1
    ahhh, noticed you're using Compat for 2008R2. Sorry, I'm out of gas here. – MguerraTorres Jun 26 '18 at 22:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.