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 insure that if part of the script fails due to an error the remaining script will complete, if there are no 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 used in an application using an OLEDB or ODBC API an error will be returned. The below script contains both DDL and DML statements and when executed as a whole will fail within SSMS.

USE master;

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

USE Errors;

CREATE TABLE Works(
number	INT);
INSERT 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 and IF statement that will create the Errors databse 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 below code demonstrates how to use the GO key word to insure that each statement is executed and evaluated separately.

USE master;

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

USE Errors;

CREATE TABLE Works(
number	INT);
GO

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

INSERT Works
VALUES(1),
 (2),
 (3);
GO
SELECT *
FROM Works;
GO

An error message is still returned showing a data type mismatch 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 a Microsoft proprietary batch directive. 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. By using the GO directive to separate statements insures that one batch containing an error will not cause the other statements to fail.

The GO directive is one of the only statements that must be on their 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.

Problem

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

Solution

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

SELECT message_id,
severity,
text
FROM sys.messages
WHERE language_id = 1033;
GO

This example returns the following abridged results::

message_id, severity, text
101,        15,       Query not allowed in Waitfor.
102,        15,       Incorrect syntax near '%.*ls'.
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 error and information messages. The view contains the messageid, language id, error severity, if the error is written to the application log and the message text. The error severity column from the sys.messages catalog viewcan be very insightful in finding user and system errors. The severity level of system and user defined messages are displayed below in Table 20-1

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

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 2005 introduced structured error handling using a BEGIN TRY . . . BEGIN CATCH block. SQL 2012 enhanced structured error handling by adding FINALLY to structured error handling. 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:

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 withint the try and catch block insuring that rather than an error being returned only an empty result set is returned. There are several functions that can be called wihtin the scope of a catch block that can be used to return error information. These functions can be returned with a select statement so rather than returning an error a result set can be returned with the information as demonstrated in the below 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, but the details are returned as a result set:

The ERROR_LINE() retunrs the approximate line number in which the error occurred. The ERROR_MESSAGE() function retunrs 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 will return NULL if called outside the scope of a CATCH block.

T-SQL structured error handling is very useful, but it does have it’s limitations. Unfortunately not all error 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:

BEGIN TRY
  SELECT
END TRY
BEGIN CATCH
END CATCH;
GO

Msg 102, Level 15, State 1, Line 2
Incorrect syntax near 'SELECT'.

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:

 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 will not be caught within try catch as well as statements that span batches or recompilation errors. Errors, or messages, with a severity of 10 or less will not be caught within the catch block as these are informational messages. The below 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 messagae as shown below:

Information ONLY

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 insure that the execution fails returning the sppropriate error message.

Solution

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

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 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, which will cause the batch to fail. In this example the throw is being used without any parameters, which can only be done within a catch block, so all error information is from the error that is being handled from within the try catch block.

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

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

Problem

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

Solution

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

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 begins and raises a divide by zero error. Immediately after the intial outer try raises and error it bypasses both the inner try and catch block and goes immediately to the outer catch block to handle the divide by zero error. The outer catch block prints the error number and the message “Outer Catch”.

To better understand the 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 outer try.

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 error then going to the inner try code. Once an error was raised in the inner try the inner catch block handles the error.

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

The above 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. Outer CATCH if an error occurs

A more complex nested try catch demonstrates how the code can dynamically handle errors based on the error number:

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()) + 'nner 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 going to the inner try code. Once an error was raised in the inner try the inner catch block handles the error.

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

The above results are drastically changed by adding a THROW in the first catch block as shown in this code:
  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 handles the error and prints out in the messages tab the concantanated string of the error number, message, severity and state as well as where the error is handled. The execution goes immediately to the outer catch block where the error string is printed out once again and then the conversion error is 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 are entered and why the error is raised. The reason is the THROW statement in the inner and outer catch block. Once the error is encountered the inner catch handles the error, but then rethrows the error. Since the error has been rethrown once leaving the inner catch block the code goes immediately to the outer catch again raising the error message that was rethrown. The outer catch handles the error with the PRINT statement and finally rethrows the error.

Problem

Certain instances require that a user defined error should 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.
  );

The above example throws a user defined eror with the message “User defined error” 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 raise error 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 below code creates a table in the tempdb 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;
CREATE TABLE Creditor(
CreditorID INT IDENTITY PRIMARY KEY,
CreditorName	VARCHAR(50)
);
GO
INSERT 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

CREATE TRIGGER Deny_Delete
ON Creditor
FOR DELETE
AS
RAISERROR('Deletions are not permitted',
  16,
  1)
ROLBACK TRAN;
GO
DELETE 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 that can also be used to throw an error. The below example demonstrates unsing the THROW statement:

THROW 50000, 'User defined error', 1;

The results of the above statement throws the following error:

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

How It Works

The throw statement is very similar to raise error, but each has their own nuissances and usefulness. The most notable difference is how both are handled within a try catch block. The solution above, 20-5, demonstrates how THROW can be used without any parameters in a try and catch block to rethrow the original error. Raise error requires that the associated error parameters be passed. By rewriting the statement from above using RAISERROR in place of THROW will return an error as demonstrated below:

BEGIN TRY
  PRINT 'Outer Try'
 BEGIN TRY
  PRINT ERROR_NUMBER() + ' Inner try'
 END TRY
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 --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';
  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 raise error 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 query above 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.

Problem

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

Solution: Use sp_addmessage to create user defined error message.

Messages can be added to an instance of SQL 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 raise error command. The below 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 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 it can be called from the raise error statement as demonstrated below.
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 raise error 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 below 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 (50001,
  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 as the “with_log” parameter was set to true. This can be verified by viewing the application log as displayed in Figure 20-1:

Anytime 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.

Problem

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

Solution: Use sp_dropmessage to remove the user defined error message

Messages can be removed from an instance of SQL using the system stored procedure sp_dropmessage. Once dropped the message will be removed from the master database and no longer be available withinthe instance. The below query first verifies that error messageid 50001 exists by querying the sys.messages catalog view and then drops the message using sp_dropmessage:

USE master
GO
SELECT message_id,
  text
FROM sys.messages
WHERE message_id = 50001;
GO
EXEC sp_dropmessage 50001;
GO
SELECT message_id,
  text
FROM sys.messages
WHERE message_id = 50001;
GO

message_id,  text
----------- ------

How It Works

The system stored procedure drops the user defined message from the master database 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.