Error in Raiserror After upgrading SqlServer 2008 R2 to 2017

All we need is an easy explanation of the problem, so here it is.

Please advise and share your solution.

In 2008 R2

DECLARE @ErrMsg varchar (25) 
SET @ErrMsg = 'Test Raiserror in 2008 R2'
RAISERROR 50002 @ErrMsg

The above statement produces the following output.

Msg 50002, Level 16, State 1, Line 4 Test Raiserror in 2008 R2 Completion time: 2022-03-12T20:12:20.1296499+03:00

In 2017

DECLARE @ErrMsg varchar (25) 
SET @ErrMsg = 'Test Raiserror in 2017'
RAISERROR 50002 @ErrMsg

The above statement produces the following output.

Msg 102, Level 15, State 1, Line 4 Incorrect syntax near ‘50002’. Completion time: 2022-03-12T20:13:24.4256066+03:00

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

RAISERROR 50002 @ErrMsg

This form of RAISERROR syntax was deprecated many versions ago and removed from the product entirely in the SQL Server 2012 release. From the Discontinued Database Engine Functionality in SQL Server 2012 page:

Discontinued feature: RAISERROR in the format RAISERROR integer
‘string’ is discontinued.

Replacement: Rewrite the statement using the current RAISERROR(…)
syntax.

As an alternative to the current RAISERROR syntax, you could instead use THROW as below. This allows use of the same same user-defined error number without having to add message to sys.messages (which RAISERROR requires). Be sure to terminate the preceding statement with a semi-colon.

THROW 50002, @ErrMsg, 1;

Method 2

You’re missing a comma between the parameters of your RAISERROR call in RAISERROR 50002 @ErrMsg. It should actually be RAISERROR 50002, @ErrMsg to be syntactically correct.

Per the docs on RAISERROR:

RAISERROR msg_id, msg_str

Also please note that RAISERROR is a little bit deprecated in itself, as the docs caution:

The RAISERROR statement does not honor SET XACT_ABORT. New applications should use THROW instead of RAISERROR.

As recommended you should generally use the THROW keyword instead, e.g:

DECLARE @ErrMsg varchar (25) 
SET @ErrMsg = 'Test Raiserror in 2008 R2'
;THROW 50002, @ErrMsg, 0;

Note the required preceding semicolon before the THROW keyword. Though you should generally terminate all statements with a semicolon anyway.

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply