In this chapter you’ll learn several error handling methods in T-SQL including structured error handling.

21-1. Handling Batch Errors

Problem

You have a script containing numerous Data Definition Language (DDL) and Data Manipulation Language (DML) statements that completely fail to run. You need to ensure that if part of the script fails due to an error the remaining script will complete, if there are no other errors.

Solution

A single script can contain multiple statements, and if run as a single batch the entire script will fail. When using SSMS or SQLCMD, batches can be separated with the GO command, but when implemented in an application using an OLEDB or ODBC API, an error will be returned. The following script contains both DDL and DML statements, and when executed as a whole will fail within SSMS:

USE master;
GO

IF EXISTS(SELECT 1/0 FROM sys.databases WHERE name = 'Errors')
BEGIN
        DROP DATABASE Errors;
        CREATE DATABASE Errors;
END
ELSE
BEGIN
        CREATE DATABASE Errors;
END

USE Errors;
GO

CREATE TABLE Works(
number  INT);

INSERT INTO Works
VALUES(1),
        ('A'),
        (3);

SELECT *
FROM Works;

The script returns immediately with an error indicating that the Errors database does not exist.

Msg 911, Level 16, State 1, Line 11
Database 'Errors' does not exist. Make sure that the name is entered correctly.

Reviewing the initial DDL statement shows the use of an IF statement block that will create the Errors database if it does not exist, so this may seem a bit confusing. The fact is that SQL Server evaluates the entire script as a single batch and returns the error since the USE statement references a database that does not exist.

This type of error can be easily overcome by separating each statement with a batch directive. The following code demonstrates how to use the GO keyword to ensure that each statement is executed and evaluated separately:

USE master;
GO

IF EXISTS(SELECT 1/0 FROM sys.databases WHERE name = 'Errors')
BEGIN
        DROP DATABASE Errors;
        CREATE DATABASE Errors;
END
ELSE
BEGIN
        CREATE DATABASE Errors;
END
GO

USE Errors;
GO

CREATE TABLE Works(number INT);
GO

INSERT INTO Works
VALUES(1),
        ('A'),
        (3);
GO

INSERT INTO Works
VALUES(1),
        (2),
        (3);
GO

SELECT *
FROM Works;

An error message is still returned showing a data type mismatch that is trying to insert the character “A” into the Errors table; however, all other statements complete, as is shown with the results of the select statement.

Msg 245, Level 16, State 1, Line 2
Conversion failed when converting the varchar value 'A' to data type int.

number
-----------
   1
   2
   3

How It Works

The GO statement is the default batch directive from Microsoft. SQL Server can accept multiple T-SQL statements for execution as a batch. The statements in the batch are parsed, bound, and compiled into a single execution. If any of the batch fails to parse or bind, then the query fails. Using the GO directive to separate statements ensures that one batch containing an error will not cause the other statements to fail.

The GO directive is one of only a few statements that must be on its own line of code. For example the following statement would fail:

SELECT *
FROM Works;
GO

A fatal scripting error occurred.
Incorrect syntax was encountered while parsing GO.

21-2. What Are the Error Numbers and Messages Within SQL?

Problem

You need to view the error numbers and messages that are contained within an instance of SQL Server.

Solution

SQL Server contains a catalog view that can be used to query the error messages contained within that specific instance. The view contains all messages for a number of languages, so it is best to filter the query based on the language_id. The following query will return all United States English messages:

USE master;
GO
SELECT sl.alias as LangAlias,
           message_id,
           severity,
           text
FROM sys.messages m
        INNER JOIN sys.syslanguages sl
                ON m.language_id = sl.msglangid
WHERE sl.name = 'us_english'
ORDER BY sl.name;
GO

This example returns the following abridged results:

LangAlias     message_id       severity        text
English       101      15      Query not allowed in Waitfor.
English       102      15      Incorrect syntax near '%.*ls'.
English       103      15      The %S_MSG that starts with '%.*ls' is too long. Maximum length is %d.

How It Works

The catalog view maintains a list of all system-and user-defined error and information messages. The view contains the message ID, language ID, error severity, if the error was written to the application log, and the message text. The error severity column from the sys.messages catalog view can be very insightful in finding user and system errors. The severity level of system- and user-defined messages are displayed in Table 21-1.

Based on the severity level, targeting and debugging a query or process can be made easier, since it can be ascertained if the error is user or system based.

21-3. How Can I Implement Structured Error Handling in My Queries?

Problem

You are required to write T-SQL statements that have structured error handling so that the application will not incur a runtime error.

Solution

SQL Server has implemented structured error handling using a BEGIN TRY...BEGIN CATCH block. Structured error handling can be easily implemented within a query by placing the query within the BEGIN TRY block immediately followed by the BEGIN CATCH block:

USE tempdb;
GO

BEGIN TRY
 SELECT 1/0  --This will raise a divide by zero error if not handled
END TRY
BEGIN CATCH
END CATCH;
GO

The outcome is that no error or results are returned.

(0 row(s) affected)

How It Works

A query error is handled within the try and catch block, ensuring that, rather than an error being returned, an empty result set is returned. There are several functions that can be called within the scope of a catch block so as to return error information. These functions can be used with a SELECT statement. So, rather than returning an error, a result set can be returned with the desired information, as demonstrated in the following code:

BEGIN TRY
  SELECT 1/0 --This will raise a divide-by-zero error if not handled
END TRY
BEGIN CATCH
  SELECT ERROR_LINE() AS 'Line',
                 ERROR_MESSAGE() AS 'Message',
                 ERROR_NUMBER() AS 'Number',
                 ERROR_PROCEDURE() AS 'Procedure',
                 ERROR_SEVERITY() AS 'Severity',
                 ERROR_STATE() AS 'State'
END CATCH;

The results are displayed below, showing that an error is not encountered, and the details are returned as a result set.

Line    Message                             Number    Procedure       Severity      State
------  --------------------------------    --------  --------------  -----------   ------
2       Divide by zero error encountered.   8134      NULL             16           1

ERROR_LINE() returns the approximate line number at which the error occurred. The ERROR_MESSAGE() function returns the text message of the error that is caught in the CATCH block. The ERROR_NUMBER() function returns the error number that caused the error. The ERROR_PROCEDURE() will return the name of the stored procedure or trigger that raised the error. ERROR_SEVERITY() returns the severity, irrespective of how many times it is run or where it is caught within the scope of the CATCH block. The ERROR_STATE() returns the state number of the error message that caused the CATCH block to be run, and it will return NULL if called outside the scope of a CATCH block.

T-SQL’s structured error handling is very useful, but it does have its limitations. Unfortunately, not all errors can be captured within a try catch block. For example, compilation errors will not be caught. This is easily demonstrated by placing syntactically incorrect statements within a try catch block, as demonstrated here:

USE tempdb;
GO

BEGIN TRY
  SELCT
END TRY
BEGIN CATCH
END CATCH;

GOMsg 102, Level 15, State 1, Line 2
Incorrect syntax near 'SELCT'.

Since SELECT was misspelled, the query could not be compiled. Binding errors will also not be caught within a try catch block, as is demonstrated here:

USE tempdb;
GO

BEGIN TRY
  SELECT NoSuchTable
END TRY
BEGIN CATCH
END CATCH;
GO

Msg 207, Level 16, State 1, Line 3
Invalid column name 'NoSuchTable'.

Error messages with a severity of 20 or higher, statements that span batches, and recompilation errors will not be caught within try catch. Errors or messages with a severity of 10 or less will not be caught within the catch block, as these are informational messages. The following code demonstrates using RAISERROR to throw an informational message within a try catch block:

BEGIN TRY
  RAISERROR('Information ONLY', 10, 1)
END TRY
BEGIN CATCH
END CATCH;
GO
The messages tab of SSMS returns the following message.

Information ONLY

21-4. How Can I Use Structured Error Handling, but Still Return an Error?

Problem

You are required to write T-SQL statements that have structured error handling, but will also need to return the system- or user-defined error to ensure that the execution failures return the appropriate error message.

Solution

SQL Server 2012 introduced the THROW statement, which can be included in a try and catch block. The following code demonstrates how using THROW in the catch block will still return a divide-by-zero error:

USE tempdb;
GO

BEGIN TRY
   SELECT 1/0
END TRY
BEGIN CATCH
    PRINT 'In catch block.';
    THROW;
END CATCH;

(0 row(s) affected)
In catch block.
Msg 8134, Level 16, State 1, Line 2
Divide by zero error encountered.

How It Works

The try and catch block works as outlined in the solution above, with the only difference being that the THROW statement is contained in the catch block. The result is that the message “In catch block” is printed in the messages tab, followed by the resulting error being raised by the THROW statement.

The severity of any error passed in the THROW statement is set to 16. THROWing the error will then cause the batch to fail. In this example, the throw is being used without any parameters (commonly referred to as rethrowing the error), which can only be done within a catch block, so all error information is thus from the error that is being handled within the try catch block.

A custom error can be thrown based on the error that is raised. The following code demonstrates how to throw an error based upon the error number that is returned:

USE tempdb;
GO

BEGIN TRY
   SELECT 1/0
END TRY
BEGIN CATCH
        IF (SELECT @@ERROR) = 8134
        BEGIN;
        THROW 51000, 'Divide by zero error occurred', 10;
          END
          ELSE
        THROW 52000, 'Unknown error occurred', 10;

END CATCH; (0 row(s) affected)
Msg 51000, Level 16, State 10, Line 7
Divide by zero error occurred

21-5. Nested Error Handling

Problem

There may be times when you will be required to use structured error handling, but you will need to ensure that errors are handled in either the try or catch block.

Solution

Try and catch blocks can be nested within either the TRY or the CATCH blocks. The following example displays nesting inside the TRY block:

USE tempdb;
GO

BEGIN TRY
  SELECT 1/0 --This will raise a divide-by-zero error if not handled
        BEGIN TRY
                PRINT 'Inner Try'
        END TRY
        BEGIN CATCH
                PRINT CONVERT(CHAR(5), ERROR_NUMBER()) + 'Inner Catch'
        END CATCH
END TRY
BEGIN CATCH
  PRINT CONVERT(CHAR(5), ERROR_NUMBER()) + 'Outer Catch'
END CATCH;
GO

(0 row(s) affected)
8134 Outer Catch

How It Works

The outer try block began and raised a divide-by-zero error. Immediately after the initial outer catch tried raising an error, it bypassed both the inner try and catch blocks and went immediately to the outer catch block to handle the divide-by-zero error. The outer catch block printed the error number and the message “Outer Catch.”

To better understand how this works, examine the code below that reverses the code between the outer and inner try, causing the error to be raised within the inner catch nested within the outer try block:

USE tempdb;
GO

BEGIN TRY
  PRINT 'Outer Try'
    BEGIN TRY
                SELECT 1/0 --This will raise a divide-by-zero error if not handled
    END TRY
    BEGIN CATCH
                PRINT CONVERT(CHAR(5), ERROR_NUMBER()) + 'Inner Catch'
    END CATCH
END TRY
BEGIN CATCH
  PRINT CONVERT(CHAR(5), ERROR_NUMBER()) + 'Outer Catch'
END CATCH;
GO

The results show that the outer try executed without an error, then proceeded to the inner try code. Once an error was raised in the inner try, the inner catch block handled the error.

Outer Try
(0 row(s) affected)
8134 Inner Catch

This demonstrates the order in which a nested TRY...CATCH will occur when nested in the try block.

1. Outer TRY block
2. Outer CATCH block if an error occurs
3. Inner TRY block
4. Inner CATCH if an error occurs

A more complex nested TRY...CATCH demonstrates how the code can dynamically handle errors based on the error number:

USE tempdb;
GO

BEGIN TRY
  PRINT 'Outer Try'
    BEGIN TRY
                PRINT ERROR_NUMBER() + ' Inner try'
    END TRY
    BEGIN CATCH
        IF ERROR_NUMBER() = 8134
                   PRINT CONVERT(CHAR(5), ERROR_NUMBER()) + ' Inner Catch Divide by zero'
        ELSE
            BEGIN
            PRINT CONVERT(CHAR(6), ERROR_NUMBER()) + ' '
                         + ERROR_MESSAGE() +
              CONVERT(CHAR(2), ERROR_SEVERITY()) + ' ' +
              CONVERT(CHAR(2), ERROR_STATE()) + ' INITIAL Catch';
            END
    END CATCH;
END TRY
BEGIN CATCH
    IF ERROR_NUMBER() = 8134
        PRINT CONVERT(CHAR(5), ERROR_NUMBER()) + ' Outer Catch Divide by zero'
    ELSE
        BEGIN
        PRINT CONVERT(CHAR(6), ERROR_NUMBER()) + ' ' + ERROR_MESSAGE() +
              CONVERT(CHAR(2), ERROR_SEVERITY()) + ' ' +
              CONVERT(CHAR(2), ERROR_STATE()) + ' OUTER Catch';
        THROW
        END
END CATCH;

The results show that the outer try executed without error, then went to the inner try code. Once an error was raised in the inner try, the inner catch block handled the error.

Outer Try
245    Conversion failed when converting the varchar value ' Inner try' to data type int.161 INITIAL Catch

These results are drastically changed by adding a THROW in the first catch block, as shown in this code:

USE tempdb;
GO

BEGIN TRY
  PRINT 'Outer Try'
    BEGIN TRY
                PRINT ERROR_NUMBER() + ' Inner try'
    END TRY
    BEGIN CATCH
        IF ERROR_NUMBER() = 8134
                   PRINT CONVERT(CHAR(5), ERROR_NUMBER()) + ' Inner Catch Divide by zero'
        ELSE
            BEGIN
            PRINT CONVERT(CHAR(6), ERROR_NUMBER()) + ' '
                         + ERROR_MESSAGE() +
              CONVERT(CHAR(2), ERROR_SEVERITY()) + ' ' +
              CONVERT(CHAR(2), ERROR_STATE()) + ' INITIAL Catch';
           THROW --This THROW is added in the initial CATCH
           END
    END CATCH;
END TRY
BEGIN CATCH
    IF ERROR_NUMBER() = 8134
        PRINT CONVERT(CHAR(5), ERROR_NUMBER()) + ' Outer Catch Divide by zero'
    ELSE
        BEGIN
        PRINT CONVERT(CHAR(6), ERROR_NUMBER()) + ' ' + ERROR_MESSAGE() +
              CONVERT(CHAR(2), ERROR_SEVERITY()) + ' ' +
              CONVERT(CHAR(2), ERROR_STATE()) + ' OUTER Catch';
        THROW
        END
END CATCH;

The results now show that the outer try executed without error and proceeded to the inner try code. Once an error was raised in the inner try, the inner catch block handled the error and printed to the messages tab the concatenated string of the error number, message, severity, and state, as well as where the error was handled. The execution went immediately to the outer catch block, where the error string was printed out again, and then the conversion error was raised.

Outer Try
245    Conversion failed when converting the varchar value ' Inner try' to data type int.16 1 INITIAL Catch
245    Conversion failed when converting the varchar value ' Inner try' to data type int.16 1 OUTER Catch
Msg 245, Level 16, State 1, Line 5
Conversion failed when converting the varchar value ' Inner try' to data type int.

The confusing part of the execution is why both catch blocks were entered and why the error was raised. The reason is the THROW statement in the inner and outer catch blocks. Once the error was encountered the inner catch handled the error, but then rethrew the error. Since the error was rethrown once leaving the inner catch block, the code went immediately to the outer catch, again raising the error message that was rethrown. The outer catch handled the error with the PRINT statement and finally rethrew the error.

21-6. Throwing an Error

Problem

Certain instances require that a user-defined error be thrown.

Solution #1: Use RAISERROR to throw an error

Throwing an error within a block of code is as simple as using the RAISERROR statement:

RAISERROR ('User-defined error', -- Message text.
               16, -- Severity.
               1 -- State.
               );

This example throws a user-defined error with the message “User-defined error” and with a severity of 16 and state of 1:

Msg 50000, Level 16, State 1, Line 1
User-defined error

How It Works

User-defined errors must have an error number that is equal to or greater than 50000, so if a number isn’t defined in the RAISERROR statement the default error number will be 50000.

A more practical example can be given by using RAISERROR in a DELETE trigger on a table that does not allow the deletion of records. Using RAISERROR can stop the transaction from occurring by raising a user-defined error that specifies that deletions are not permitted.

The following code creates a table in the tempdb database called Creditor and then creates an after delete trigger that raises an error. The result is that any attempt to delete a record will return an error with a message explaining that deletions are not permitted:

USE tempdb;
GO

CREATE TABLE Creditor(
CreditorID              INT IDENTITY PRIMARY KEY,
CreditorName    VARCHAR(50)
);
GO

INSERT INTO Creditor
VALUES('You Owe Me'),
        ('You Owe Me More'),
GO

SELECT *
FROM Creditor;
GO

Executing the above query shows that the table is created and populated with two rows.

CreditorID,        CreditorName,
---------------    -------------------
1                  You Owe Me
2                  You Owe Me More

USE tempdb;
GO

CREATE TRIGGER Deny_Delete
ON Creditor
FOR DELETE
AS
RAISERROR('Deletions are not permitted',
        16,
                1)
ROLLBACK TRANSACTION;
GO

DELETE
FROM Creditor
WHERE CreditorID = 1;
GO

Once the trigger is created and a deletion is attempted, the transaction fails with two errors. The first error is the error thrown using RAISERROR, and the second is thrown from the ROLLBACK command that is within the trigger.

Msg 50000, Level 16, State 1, Procedure Deny_Delete, Line 6
Deletions are not permitted
Msg 3609, Level 16, State 1, Line 1
The transaction ended in the trigger. The batch has been aborted.

SELECT *
FROM Creditor;
GO

The results of selecting all the records from the Creditor table shows that both rows are still in the table.

CreditorID         CreditorName
---------------    -------------------
1                  You Owe Me
2                  You Owe Me More

Solution #2: Use THROW to throw an error

SQL 2012 introduced the THROW statement, which can also be used to throw an error. The following example demonstrates using the THROW statement:

THROW 50000, 'User-defined error', 1;

The preceding statement produces the following error.

Msg 50000, Level 16, State 1, Line 1
User-defined error

How It Works

The THROW statement is very similar to RAISERROR, but each has its own nuances. The most notable difference is in how each is handled within a TRY...CATCH block. For example, THROW stops the batch if not trapped in the CATCH. In Recipe 21-5, I demonstrated how THROW can be used without parameters in a TRY...CATCH block to rethrow the original error. RAISERROR requires that the associated error parameters be passed. Rewriting the example from 21-5, using RAISERROR, without parameters, in place of THROW will return an error as demonstrated here:

USE tempdb;
GO

BEGIN TRY
  PRINT 'Outer Try'
    BEGIN TRY
                PRINT ERROR_NUMBER() + ' Inner try'
    END TRY
    BEGIN CATCH
        IF ERROR_NUMBER() = 8134
                   PRINT CONVERT(CHAR(5), ERROR_NUMBER()) + ' Inner Catch Divide by zero'
        ELSE
            BEGIN
            PRINT CONVERT(CHAR(6), ERROR_NUMBER()) + ' '
                         + ERROR_MESSAGE() +
              CONVERT(CHAR(2), ERROR_SEVERITY()) + ' ' +
              CONVERT(CHAR(2), ERROR_STATE()) + ' INITIAL Catch';
           RAISERROR
           END
    END CATCH;
END TRY
BEGIN CATCH
    IF ERROR_NUMBER() = 8134
        PRINT CONVERT(CHAR(5), ERROR_NUMBER()) + ' Outer Catch Divide by zero'
    ELSE
        BEGIN
        PRINT CONVERT(CHAR(6), ERROR_NUMBER()) + ' ' + ERROR_MESSAGE() +
              CONVERT(CHAR(2), ERROR_SEVERITY()) + ' ' +
              CONVERT(CHAR(2), ERROR_STATE()) + ' OUTER Catch';
        RAISERROR
        END
END CATCH;
Msg 156, Level 15, State 1, Line 15
Incorrect syntax near the keyword 'END'.

Msg 156, Level 15, State 1, Line 27
Incorrect syntax near the keyword 'END'

Although RAISERROR can be used in place of THROW in such a case, it requires substantially more code, and the end result still provides a different error number:

BEGIN TRY
  PRINT 'Outer Try'
    BEGIN TRY
                PRINT ERROR_NUMBER() + ' Inner try'
    END TRY
    BEGIN CATCH
        DECLARE @error_message AS VARCHAR(500) = ERROR_MESSAGE()
        DECLARE @error_severity AS INT = ERROR_SEVERITY()
        DECLARE @error_state AS INT = ERROR_STATE()

        IF ERROR_NUMBER() = 8134
                   PRINT CONVERT(CHAR(5), ERROR_NUMBER()) + ' Inner Catch Divide by zero'
        ELSE
            BEGIN
            PRINT CONVERT(CHAR(6), ERROR_NUMBER()) + ' '
                         + ERROR_MESSAGE() +
              CONVERT(CHAR(2), ERROR_SEVERITY()) + ' ' +
              CONVERT(CHAR(2), ERROR_STATE()) + ' INITIAL Catch';
           RAISERROR(@error_message,@error_severity,@error_state);
           END
    END CATCH;
END TRY
BEGIN CATCH
    IF ERROR_NUMBER() = 8134
        PRINT CONVERT(CHAR(5), ERROR_NUMBER()) + ' Outer Catch Divide by zero'
    ELSE
        BEGIN
        PRINT CONVERT(CHAR(6), ERROR_NUMBER()) + ' ' + ERROR_MESSAGE() +
              CONVERT(CHAR(2), ERROR_SEVERITY()) + ' ' +
              CONVERT(CHAR(2), ERROR_STATE()) + ' OUTER Catch';
        RAISERROR(@error_message,@error_severity,@error_state);

        END
END CATCH;

The results appear almost identical to the example from 21-5 using THROW, except for the error number.

Outer Try
245    Conversion failed when converting the varchar value ' Inner try' to data type int.16 1 INITIAL Catch
50000  Conversion failed when converting the varchar value ' Inner try' to data type int.16 1 OUTER Catch
Msg 50000, Level 16, State 1, Line 33
Conversion failed when converting the varchar value ' Inner try' to data type int.

21-7. Creating a User-Defined Error

Problem

A user-defined error message needs to be created to be used from RAISERROR.

Solution

Messages can be added to an instance of SQL Server using the system-stored procedure sp_addmessage. User-defined messages are added to an instance and can be viewed from the sys.messages system catalog view and called from either THROW or the RAISERROR command. The following query creates a user-defined message:

USE master
GO
EXEC sp_addmessage 50001, 16,
   N'This is a user-defined error that can be corrected by the user';
GO

This message will then be made available within an instance of SQL Server and can be viewed within the sys.messages catalog view:

SELECT message_id,
         text
FROM sys.messages
WHERE message_id = 50001;
GO

message_id                  text
-----------------           ------
50001                       This is a user-defined error that can be corrected by the user

Once the message is created in the instance of SQL Server, it can be called from the RAISERROR statement:

RAISERROR (50001,16,1);
GO

Msg 50001, Level 16, State 1, Line 1
This is a user-defined error that can be corrected by the user

How It Works

The system-stored procedure adds the user-defined message to the master database, where it can be called by using the RAISERROR command. The error number must be 50000 or greater, but the message, severity, and whether the message is logged to the application log can be specified when adding the message to the master database.

The next example adds a message of severity 16, user caused, to the master database, but will be logged to the application log:

USE master
GO
sp_addmessage @msgnum = 50002 ,
        @severity = 16 ,
        @msgtext = 'User error that IS logged',
        @with_log = 'TRUE';
GO

RAISERROR (50002,16,1);
GO

Msg 50002, Level 16, State 1, Line 1
User error that IS logged

Despite the severity of this error being set to 16, user defined, the error will still be logged to the Windows application log, because the “with_log” parameter was set to TRUE. This can be verified by viewing the application log as displayed in Figure 21-1 (or even from within the SQL Server Error Log):

Any time the alert is called it will still be recorded in the application log, which provides a great deal of functionality in administration, as it can be used to fire off events from SQL alerts. This demonstrates how user-defined errors can be created and leveraged for both development and administrative purposes.

21-8. Removing a User-Defined Error

Problem

A user-defined error has been created and needs to be removed.

Solution

Messages can be removed from an instance of SQL Server by using the system-stored procedure sp_dropmessage. Once dropped, the message will be removed from the master database and will no longer be available within the instance. This example first verifies that an error with message_id 50001 exists by querying the sys.messages catalog view and then drops the message using sp_dropmessage:

USE master
GO

IF EXISTS ( SELECT 1/0 FROM sys.messages WHERE message_id = 50001)
BEGIN
  EXEC sp_dropmessage 50001;
END
GO

/* Confirm the error has been deleted */
SELECT message_id,
         text
FROM sys.messages
WHERE message_id = 50001;
GO

The results of the query show that the error has indeed been deleted.

message_id                                text
-----------------                         ------

How It Works

The system-stored procedure drops the user-defined message from the master database, thus removing it from the entire instance of SQL Server. Any future attempts to call the error with either RAISERROR or THROW will result in an error indicating that the message does not exist:

RAISERROR(50001,16,1);
GO

Msg 18054, Level 16, State 1, Line 1
Error 50001, severity 16, state 1 was raised, but no message with that error number was found in sys.messages. If error is larger than 50000, make sure the user-defined message is added using sp_addmessage.