SQL Server Portal

Developers mostly prefer the shortest possible code and they frequently use it in their applications. I have written a shortest possible code for RAISERROR in this article but unfortunately this shortest code has been discontinued from SQL Server 2012. The most important concern is not that the shortest possible code is discontinued but what is the replacement of that code.

Given below is the shortest possible code, we use in the earlier versions of SQL Server 2005/2008.

--This script is compatible with SQL Server 2005/ 2008.
USE tempdb
GO
RAISERROR 14243 'This is a test message'
GO
--OUTPUT

Msg 14243, Level 16, State 1, Line 1
This is a test message

As you can see that above script is executed successfully. Now lets execute the above script in SQL Server 2012.

--This script is NOT compatible with SQL Server 2012.
USE tempdb
GO
RAISERROR 14243 'This is a test message'
GO
--OUTPUT

Msg 102, Level 15, State 1, Line 1
Incorrect syntax near ‘14243’.

Ooopps…… I am unable to execute it in SQL Server 2012. As per MSDN, we need to rewrite the statement using the current RAISERROR syntax.

Lets rewrite it step by step.

Step 1 :
This step is not mandatory, in case if your error message is already available in sys.messages.
Given below is a script to add error messages in sys.messages.

EXEC sys.sp_addmessage
@msgnum   = 80000
,@severity = 10
,@msgtext  = N'This is a test message.'
,@lang = 'us_english';

Step 2 :
In this step we need to execute the RAISERROR using new syntax.
Given below is the script.

USE tempdb
GO
RAISERROR (80000, -- Message id,
10, -- Severity,
1) -- State
GO
--OUTPUT

This is a test message.

Cleaning :

sp_dropmessage @msgnum = 80000;
GO