Error in Raiserror After upgrading SqlServer 2008 R2 to 2017

by

The question:

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

The Solutions:

Below are the methods you can try. The first solution is probably the best. Try others if the first one doesn’t work. Senior developers aren’t just copying/pasting – they read the methods carefully & apply them wisely to each case.

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.


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 Comment