Microsoft® SQL Server® 2012 Bible

422

Part IV: Programming with T-SQL

END TRY

BEGIN CATCH

THROW;

END CATCH;

Result:

Msg 8134, Level 16, State 1, Line 3

Divide by zero error encountered.

Finally, if you want to migrate from RAISERROR to THROW but have invested a lot of time

and effort into populating sys.messages with meaningful error information, don’t fret.

The FORMATMESSAGE statement enables you to THROW customized error messages con-

tained in sys.messages.

USE AdventureWorks2012;

GO

IF NOT EXISTS (SELECT * FROM sys.messages WHERE message_id = 50003)

BEGIN

EXECUTE sp_addmessage 50003, 16, 'Custom message for sys.messages';

END

GO

DECLARE @Message nvarchar(2000);

SELECT @Message = FORMATMESSAGE(50003);

BEGIN TRY

INSERT INTO Person.BusinessEntity

(BusinessEntityID, rowguid, ModifiedDate)

VALUES(1, newid(), getdate());

END TRY

BEGIN CATCH

THROW 50003, @Message, 1;

END CATCH;

Result:

Msg 50003, Level 16, State 1, Line 12

Custom message courtesy of sys.messages

Table 16-4 breaks down the primary differences between RAISERROR and THROW.

c16.indd 422c16.indd 422 7/30/2012 5:38:12 PM7/30/2012 5:38:12 PM

http://www.it-ebooks.info