How to use variables in SQL raiserror

Question

I am trying to show my int variables in raiserror @MaxAmount and @MinAmount

Raiserror('Total Amount should be less than %s and Greater than %s',16,1,@MaxAmount,@MinAmount)

But Im getting error:

Must declare the scalar variable "@MaxAmount".

Fleda

its basic, you must declare variables before using it in query. – Normally

I did declare but there was a typo when I used it in raiserror. Solved! – Fleda

Answer 1

%s is used for varchar and your variable is of type int hence you need to try to use correct format specifier ie, %d

DECLARE @MaxAmount int = 16;
DECLARE @minAmount int = 1;
Raiserror('Total Amount should be less than %d and Greater than %d',@MaxAmount,@MinAmount)

Check RAISEERROR for details.

Shag

This didn't work for me, I had to additionally supply severity and state parameters as per @Edward Comeau's answer and THEN the max/min amount params otherwise the message was: Total Amount should be less than (null) and Greater than (null) – Corrigible

Answer 2

You need to use %I for integers and as mentioned, declare the variables before use.

declare @MaxAmount int, @MinAmount int
select @MaxAmount = 50, @MinAmount = 5
Raiserror('Total Amount should be less than %i and Greater than %i',16,1,@MaxAmount,@MinAmount)

Bethea

Answer 3

I think you try in this way:

DECLARE @MaxAmount int = 16;
DECLARE @MinAmount int = 1;

Raiserror('Total Amount should be less than %d and Greater than %d',@MaxAmount,@MinAmount)

If you want further information about RAISERROR, go here

Daye

Answer 4

The above solutions did not work for me, because you have to declare also severity and state. Without it the result will be like this:

Total Amount should be less than (null) and Greater than (null)

You can try this:

DECLARE @MaxAmount int = 16;
DECLARE @MinAmount int = 1;

Raiserror('Total Amount should be less than %d and Greater than %d', 16, 1, @MaxAmount, @MinAmount)

Scheffler

I appreciate you acknowledging the previous answers and highlighting how your answer differs from them. That’s especially useful when responding to old questions with established answers, as in this case. That said, @Edward-Comeau’s answer from five years ago does include severity and state. – Psychoanalysis