This post is a continuation of Part 9. In this post we are going to discuss add more error checking and handling with RAISERROR inside a TRY…CATCH block. The next post will list the code.

In our first stored procedure that deletes all of the rows in the table we may ask ourselves: What if the table is empty? What if the incoming file is empty? What if the incoming file’s format or layout has changed? These conditions should raise an errors and report back to the user the reason for the error. How critical is the error? Should we jump out of the TRY in the TRY…CATCH block into the CATCH block?

In our code back in Part 9 and our explanation of the project back in Part 8, we note that there are four procedures that are called by the one procedure named dbo.AllProcedures. A good place to start with our new error trapping code is in the first of the four procedure named dbo.DeleteRowsInReport. The code we decide on is shown in the next post. First we will look at the RAISERROR statement.

RAISERROR

First we will have a general discussion on the syntax for RAISERROR based on the Microsoft website. MSDN says “Generates an error message and initiates error processing for the session. RAISERROR can either reference a user-defined message stored in the sys.messages catalog view or build a message dynamically. The message is returned as a server error message to the calling application or to an associated CATCH block of a TRY…CATCH construct. New applications should use THROW instead.”

We however are going to use RAISERROR, not THROW. Note that RAISERROR is not deprecated. There are things you can do with RAISERROR that you cannot do with THROW. (WITH NOWAIT, WITH NOLOG, set severity level.) The syntax for RAISERROR is shown below. For posts at this site, we will only be looking at the syntax for SQL Server and Azure Database, not Azure SQL Data Warehouse and Parallel Data Warehouse.

-- Syntax for SQL Server and Azure SQL Database  
RAISERROR ( { msg_id | msg_str | @local_variable }  
    { ,severity ,state }  
    [ ,argument [ ,...n ] ] )  
    [ WITH option [ ,...n ] ] 

-- Syntax for Azure SQL Data Warehouse and Parallel Data Warehouse  
RAISERROR ( { msg_str | @local_variable }  
    { ,severity ,state }  
    [ ,argument [ ,...n ] ] )  
    [ WITH option [ ,...n ] ]  

Error Severity

ERROR_SEVERITY may be called anywhere within the scope of a CATCH block. ERROR_SEVERITY returns the error severity regardless of how many times it is run, or where it is run within the scope of the CATCH block. This is in contrast to functions like @@ERROR, which only returns the error number in the statement immediately after the one that causes an error, or in the first statement of a CATCH block. In nested CATCH blocks, ERROR_SEVERITY returns the error severity specific to the scope of the CATCH block in which it is referenced.

• Use error level 16 to return errors
• Use error level 10 to return warnings

BEGIN TRY  
    -- Generate a divide-by-zero error.  
    SELECT 1/0;  
END TRY  
BEGIN CATCH  
    SELECT  
        ERROR_NUMBER() AS ErrorNumber,  
        ERROR_SEVERITY() AS ErrorSeverity,  
        ERROR_STATE() AS ErrorState,  
        ERROR_PROCEDURE() AS ErrorProcedure,  
        ERROR_LINE() AS ErrorLine,  
        ERROR_MESSAGE() AS ErrorMessage;  
END CATCH;  
GO  

Here is the output in SSMS.

An article on Database Engine Severity Errors is here.

Database Engine Error Severities

An MSDN article is here. Below is a list of information when errors occur within a TRY…CATCH block.

• A TRY…CATCH construct catches all execution errors with severity greater than 10 that do not terminate the database connection.
• Errors with severity from 0 through 10 are informational messages and do not cause execution to jump from the CATCH block of a TRY…CATCH construct.
• Errors that terminate the database connection, usually with severity from 20 through 25, are not handled by the CATCH block because execution is aborted when the connection terminates.

Below is a partial list of the levels of severity from the same web page.

State

State is an integer from 0 through 255. Negative values default to 1. Values larger than 255 should not be used.
If the same user-defined error is raised at multiple locations, using a unique state number for each location can help find which section of code is raising the errors.