sql server raiseerror with numeric parameter-CodePudding

declare @institution varchar(10) = 'a', @num numeric(10,1) = 200.1
RAISERROR ('Start of strength_senior_update, @institution = %s', 0, 1,
        @institution);
RAISERROR ('Start of strength_senior_update, @institution = %s, @num=%s', 0, 1,
        @institution,@num);

the first RAISEERROR show expect result

Start of strength_senior_update, @institution = a

but the second RAISEERROR return error as below

The data type of substitution parameter 2 does not match the expected type of the format specification.

anyway, there is no format specification for numeric datatype, so how to use raiseerror with numeric parameter? must declare a varchar variable and then cast the @num to that varchar variable first?

if so trouble, why not I use print instead?

CodePudding user response：

there is no format specification for numeric datatype, so how to use raiseerror with numeric parameter? must declare a varchar variable and then cast the @num to that varchar variable first?

Correct, RAISERROR format specifications are limited so the work-around is to assign formatted message text to a variable for use with RAISERROR. The below example gleaned from the comments uses CONCAT (to convert NULL values to empty strings) and FORMAT to display the numeric value in the format of your choosing.

DECLARE @institution varchar(10) = 'a', @num numeric(10,1) = 200.1, @message varchar(2047);
SET @message = CONCAT('Start of strength_senior_update, @institution = '
    , @institution
    , ', @num='
    , FORMAT(@num, '0.0')
    );
RAISERROR (@message, 0, 1);

if so trouble, why not I use print instead?

There is no value in using RAISERROR instead of PRINT for severity zero informational messages unless you want to use the NOWAIT option or substitution parameters. PRINT allows you to specify the message expression directly without the need to assign to variable:

DECLARE @institution varchar(10) = 'a', @num numeric(10,1) = 200.1;
PRINT CONCAT('Start of strength_senior_update, @institution = '
    , @institution
    , ', @num='
    , FORMAT(@num, '0.0')
    );