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')
);