Aske B. Aske B. - 1 year ago 165
SQL Question

TSQL RaiseError incorrect syntax, following MSDN's guidelines

MSDN states the following syntax:

RAISERROR ( { msg_id | msg_str | @local_variable }
{ ,severity ,state }
[ ,argument [ ,...n ] ] )
[ WITH option [ ,...n ] ]

expects a string up to 2047 characters but truncates longer strings. It also has the possibility of substituting parameters, which truncates the message further than the number of characters provided by the values:

The error message can have a maximum of 2,047 characters. If the message contains 2,048 or more characters, only the first 2,044 are displayed and an ellipsis is added to indicate that the message has been truncated. Note that substitution parameters consume more characters than the output shows because of internal storage behavior. For example, the substitution parameter of %d with an assigned value of 2 actually produces one character in the message string but also internally takes up three additional characters of storage. This storage requirement decreases the number of available characters for message output.
When msg_str is specified, RAISERROR raises an error message with an error number of 50000.

expects a number between 0 to 25, but corrects other numbers:

Severity levels from 0 through 18 can be specified by any user. Severity levels from 19 through 25 can only be specified by members of the sysadmin fixed server role or users with ALTER TRACE permissions. For severity levels from 19 through 25, the WITH LOG option is required. Severity levels less than 0 are interpreted as 0. Severity levels greater than 25 are interpreted as 25.

expects values 0 to 255, but corrects subzero values:

[state] is an integer from 0 through 255. Negative values default to 1. Values larger than 255 should not be used.

The issue

I get the following errors when I run these queries:

RAISEERROR('Test', 20, 1);

Msg 102, Level 15, State 1, Line 1

Incorrect syntax near 'Test'.

DECLARE @err_message nvarchar(255);
SET @err_message = 'Test';
RAISEERROR(@err_message, 20, 1);

Msg 102, Level 15, State 1, Line 3

Incorrect syntax near 'RAISEERROR'.

I can execute various other queries just fine. E.g.:

THROW 50001, 'Test', 1;

Msg 50001, Level 16, State 1, Line 1


More info

produces this:

Microsoft SQL Server 2016 (RTM-GDR) (KB3194716) - 13.0.1722.0 (X64) Sep 26 2016 13:17:23 Copyright (c) Microsoft Corporation Express Edition (64-bit) on Windows 8.1 Pro 6.3 (Build 9600: )

And the MSDN syntax was for "SQL Server (starting with 2008)" and as of writing, the article was updated
October 19, 2016
, which is months after the release of the SQL Server version I'm running.

What's going on here?

Answer Source

The docs:

MSDN states the following syntax:


Your command:

RAISEERROR('Test', 20, 1);

I am always making this mistake. The command is not "Raise Error", but rather "Rais Error". I don't know why, but we're stuck with it...

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download