Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I am using RAISERROR() to provide some basic Unit Testing functionality (as here), but I am frustrated by the inability to use FLOATs in the error message. I know I can cast the float to a string, but I am using RAISERROR in every single unit test, I don't want to add another line of code for every test. (My unit tests are already wordy enough!) Is there a way to perform an inline cast/convert within the RAISERROR parameter list? Or is there another way around this deficiency?

Update: So ultimately what I wish I could do is this:

RAISERROR('Unit Test FAILED! %f', 11, 0, @floatParm)

Unfortunately, RAISERROR doesn't handle %f or floats in general. So I have to do this instead:

DECLARE @str VARCHAR(40) = CAST(@floatParm AS VARCHAR(40))
RAISERROR('Unit Test FAILED! %s', 11, 0, @str)

...which just looks like a mess when it's scattered through dozens of Unit Tests. So I'd like to boil it down to something like this:

RAISERROR('Unit Test FAILED! %s', 11, 0, CAST(@floatParm AS VARCHAR(40))

But that gets me a Incorrect syntax near 'CAST' message. I don't understand why that's illegal, but it is. Is there another "one liner" I could use here instead?

share|improve this question
    
Could you explain more please? –  Emmad Kareem Jun 29 '12 at 21:25

1 Answer 1

up vote 5 down vote accepted

Unfortunately, for whatever reason, you can't do an inline conversion in that context, and RAISERROR doesn't directly support float, again, for whatever reason.

For completeness of this answer, here is the relevant snippet from MSDN, which I'm sure you've already seen (note: it's the same text in all versions of the documentation from 2005 to 2012):

Each substitution parameter can be a local variable or any of these data types: tinyint, smallint, int, char, varchar, nchar, nvarchar, binary, or varbinary.


The only reasonable solution I can think of would be to write a stored procedure to wrap the RAISERROR call. Here's a starting point:

CREATE PROCEDURE [dbo].[MyRaiserror]
(
    @message nvarchar(2048),
    @severity tinyint,
    @state tinyint,
    @arg0 sql_variant = NULL
)
AS
BEGIN

    DECLARE @msg nvarchar(MAX) = REPLACE(@message, '%f', '%s');
    DECLARE @sql nvarchar(MAX) = N'RAISERROR(@msg, @severity, @state';

    DECLARE @int0 int, @char0 nvarchar(MAX), @bin0 varbinary(MAX);

    IF (@arg0 IS NOT NULL)
    BEGIN
        SET @sql += N', ';

        IF (SQL_VARIANT_PROPERTY(@arg0, 'BaseType') IN ('tinyint', 'smallint', 'int'))
        BEGIN
            SET @int0 = CONVERT(int, @arg0);
            SET @sql += N'@int0';
        END
        ELSE IF (SQL_VARIANT_PROPERTY(@arg0, 'BaseType') IN ('binary', 'varbinary'))
        BEGIN
            SET @bin0 = CONVERT(varbinary(MAX), @arg0);
            SET @sql += N'@bin0';
        END
        ELSE
        BEGIN
            SET @char0 = CONVERT(nvarchar(MAX), @arg0);
            SET @sql += N'@char0';
        END
    END

    SET @sql += N');';

    EXEC sp_executesql
        @sql,
        N'@msg nvarchar(2048), @severity tinyint, @state tinyint, @int0 int, @bin0 varbinary(MAX), @char0 nvarchar(MAX)',
        @msg, @severity, @state, @int0, @bin0, @char0;

END

Sadly, there's no easy way to scale this for an arbitrary number of parameters... It could probably be done using convoluted nested dynamic SQL, which would be fun to debug. I'll leave that as an exercise for the reader.

I used sql_variant on the assumption that for code uniformity reasons, the same procedure would be used everywhere, even for value types that are supported directly by RAISERROR. Also, this could be created as a temporary stored procedure if that's appropriate.

Here's what using this procedure would look like:

DECLARE @f float = 0.02345;
DECLARE @i int = 234;
DECLARE @s varchar(20) = 'asdfasdf';
DECLARE @b binary(4) = 0xA0B1C2D3;
DECLARE @d decimal(18, 9) = 152.2323;
DECLARE @n int = NULL;

EXEC [dbo].[MyRaiserror] N'Error message with no params.', 10, 1;
EXEC [dbo].[MyRaiserror] N'Float value = %f', 10, 1, @f;
EXEC [dbo].[MyRaiserror] N'Int value = %i', 10, 1, @i;
EXEC [dbo].[MyRaiserror] N'Character value = %s', 10, 1, @s;
EXEC [dbo].[MyRaiserror] N'Binary value = %#x', 10, 1, @b;
EXEC [dbo].[MyRaiserror] N'Decimal value = %f', 10, 1, @d;
EXEC [dbo].[MyRaiserror] N'Null value = %i', 10, 1, @n;

Output:

Error message with no params.
Float value = 0.02345
Int value = 234
Character value = asdfasdf
Binary value = 0xa0b1c2d3
Decimal value = 152.232300000
Null value = (null)

So the net result is that you don't get formatting ability for floats (roll your own), but you do gain the ability to output them (decimal/numeric, too!) while retaining formatting ability for the other types.

share|improve this answer
    
Wow, that's just superb! I had considered doing something like this, but was unaware of sql_variant, so I was stuck at the argument list and assumed it wasn't possible. You've taught me something very useful today. Thank you very much! –  kmote Jul 2 '12 at 3:20
    
@kmote: No problem; glad I could help. –  Jon Seigel Jul 2 '12 at 13:28

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.