Sql Server: How to use RAISERROR
- Posted by Sqltimes
- On May 31, 2014
- 0 Comments
RAISERROR is an interesting and very useful t-sql command. It allows us to send information back to the caller about the status of the current execution.
Since RAISERROR supports character substitution similar to printf function is C language, it is very valuable in some situations.
Here we see some sample examples.
Simple error message
|
1
2
3
4
5
|
---- A simple RAISERROR example - sends error message to STDOUT, without terminating the connection--RAISERROR ('Non critical error message.', 15, 1)GO |
Severity is an important parameter of this command:
- Severity levels from 0 – 10 are informational. Error message is displayed to STDOUT, but does not affect the execution flow.
- Severity levels from 11 – 19 are error triggering. They pass the control from TRY BLOCK to CATCH block.
- Severity levels from 19 – 25 can only be specified by members of sysadmin. Also WITH LOG is required for this level.
- Severity levels from 20 – 25 are considered fatal; Connection is terminated and error message is logged in Sql Server Error log and Application log.
Character Substitution example
|
1
2
3
4
5
6
7
8
9
10
11
12
13
|
---- Character Substitution--RAISERROR (N'Apples are %s; We have %d of them.', 10, -- Severity, 1, -- State, N'red', -- First argument. 10); -- Second argument.GO---- The message text returned is:--Apples are red; We have 10 of them. |
As you can see, character substitution is useful in some scenarios (above example may not be).
With Option example
In this below example, we see a way to send error/information message back to STDOUT immediately. This was covered in more detail in one of my previous post here, but I’ll briefly touch on this here.
|
1
2
3
4
5
|
---- Send message to STDOUT immediately--RAISERROR ('End of Step 1', 10, 1) WITH NOWAITGO |
Hope this helps,
_SqlTimes
0 Comments