Raise custom error message with RAISERROR in SQL Server

Question

In previous versions we raised errors in t-sql like:

RAISERROR 50000 'My Error Message'

In the latest SQL Server this syntax has been discontinued and replace with the RaiseError () syntax.

I would like to have a generic method of raising errors, and the best I could come up so far is:

sp_addmessage @msgnum = 50001,
              @severity = 10,
              @msgtext = N'My Error Message', @replace = 'REPLACE';
RAISERROR (50001, 10, 1, 'This error message is not displayed')

But I can't go and create a error message with sp_addmessage for every message, because there are 1000's.

What is the better way to raise messages with a custom message?

Answer 1

This seems to work:

RAISERROR('My Error Message',0,1)

Answer 2

Actually, RAISERROR has been deprecated in favour of THROW since SQL Server 2012. Go here for more information. One of the more amusing aspects is that it is Raiserror and not RaiseError leading to it being called "raise ror" in some circles.

Sample from BOL:

USE tempdb;
GO
CREATE TABLE dbo.TestRethrow
(    ID INT PRIMARY KEY
);
BEGIN TRY
    INSERT dbo.TestRethrow(ID) VALUES(1);
--  Force error 2627, Violation of PRIMARY KEY constraint to be raised.
    INSERT dbo.TestRethrow(ID) VALUES(1);
END TRY
BEGIN CATCH

    PRINT 'In catch block.';
    THROW;
END CATCH;

Answer 3

Use the s% wild card so that you can pass in any message you like from any of your stored procs:

IF NOT EXISTS (SELECT * FROM sys.messages m WHERE m.message_id = 62000)
EXEC sys.sp_addmessage @msgnum = 62000, 
                       @severity = 16, 
                       @msgtext = N'%s', 
                       @lang = 'us_english'

Then in your sp you can raise the error like this:

RAISERROR (62000, 16, 1, 'Error and/or Business Error Text goes here')