RaiseError in SQL Server

问题:

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?

回答1:

This seems to work:

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

回答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;

回答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 SysMessages where error = 62000) EXEC sp_addmessage @msgnum = 62000, @severity = 16, @msgtext = N'%s', @lang = 'us_english'

--Then in your sp you can raise this error:
RAISERROR (62000,16,1,'Error and/or Business Error Text goes here')