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".

its basic, you must declare variables before using it in query. — Raj Kamuni, Sep 16 '15 at 10:07
I did declare but there was a typo when I used it in raiserror. Solved! — Nuke, Sep 16 '15 at 10:11

score 16 · Accepted Answer · answered Sep 16 '15 at 10:11

16

%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.

answered Sep 16 '15 at 10:11

Rahul Tripathi

168,305
31
280
331

4

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)` – Claire Furney Nov 11 '19 at 12:55

score 10 · Answer 2 · answered Sep 16 '15 at 10:10

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)

score 1 · Answer 3 · answered Sep 16 '15 at 10:10

1

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

answered Sep 16 '15 at 10:10

Joe Taras

15,166
7
42
55

score 1 · Answer 4 · answered Jun 01 '20 at 07:14

1

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)

answered Jun 01 '20 at 07:14

Qadosh

11
2

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. – Jeremy Caney Jun 01 '20 at 08:26

How to use variables in SQL raiserror

4 Answers4