RSS Feed

SQL Server - RAISERROR VS THROW

SQL Server - RAISERROR VS THROW


Below are the 3 simple differences between RAISERROR & THROW:

1.
Different ERROR_xxxx() system functions are required with RAISERROR. For example ERROR_STATE(), ERROR_MESSAGE() etc.

An example of RAISERROR where we are dividing 1 by 0:

Output:
(0 row(s) affected)
Msg 50000, Level 16, State 1, Line 16
Divide by zero error encountered.
With THROW these are not mandatory, you are not required to pass any paramter to raise an exception.

An example of THROW where we are dividing 1 by 0:

Output:
(0 row(s) affected)
Msg 8134, Level 16, State 1, Line 2
Divide by zero error encountered.
2.
RAISERROR() shows the line number where the RAISERROR statement was executed.

Below you can see as the RAISERROR statement is executed on line 17, hence in output line 17 is shown.

Output:
(0 row(s) affected)
err num: 2627
line num: 5
Msg 50000, Level 14, State 1, Procedure Proc_B, Line 17
Violation of PRIMARY KEY constraint 'PK__B__3214EC2719E6EB43'. 
Cannot insert duplicate key in object 'dbo.B'. The duplicate key value is (3).

THROW statement by default shows the exact line where the exception has occurred.

With THROW, line number is where the error has occurred. So it shows line 5 in the output below which seems more appropriate as compared to RAISERROR. 

Output:
(0 row(s) affected)
err num: 2627
line num: 6
Msg 2627, Level 14, State 1, Procedure Proc_B, Line 5
Violation of PRIMARY KEY constraint 'PK__B__3214EC2719E6EB43'. 
Cannot insert duplicate key in object 'dbo.B'. The duplicate key value is (3).
3.
If you are passing a msg_id to RAISERROR, then the ID must be defined in sys.messages.

RAISERROR (80000, 16, 1) 
Output:
Msg 18054, Level 16, State 1, Line 1
Error 80000, severity 16, state 1 was raised, but no message with that error number 
was found in sys.messages.
If error is larger than 50000, make sure the user-defined message is added using sp_addmessage.

EXEC sys.sp_addmessage 80000, 16, 'Personal Error Message'

RAISERROR (80000, 16, 1)
Output:
Msg 80000, Level 16, State 1, Line 1
Personal Error Message

In case of THROW, error_number parameter does not have to be defined in sys.messages.

THROW 80000, 'Personal Error Message', 1 
Output:
Msg 80000, Level 16, State 1, Line 1
Personal Error Message