Building the SQL Error Engine

In this second post focused on a T-SQL framework (find part one here), we will build the plumbing to leverage our first feature of the framework, the error engine. This feature  will runs like an engine and do the manual work we do not want to do. To make it dynamic, the feature must be data-driven using small tools that work with each other. 

We will address architecture, database design, functions, and other coding principles, following some best practices. We are not going to describe each one in detail because that is out of scope for this project. That would make this post even longer. If you find that we have glanced over any topic that you feel should be discussed in more detail, please leave some feedback. If there is enough interest, we may publish a blog solely on that topic.

With this blog post, we will:

  1. Create a new database to hold our framework
  2. Create code tables, an error definition table, and an error logging table
  3. Populate our base data-driven tables (the code tables and the error definition table)
  4. Create a user-defined function to get our codes for us
  5. Add a new error message within the SQL engine
  6. Create a new template that will leverage the coding effort above

A lot of work is ahead of us, and there won’t necessarily be a lot of explanation.  For instance, we will create a database that will hold all stored procedures that will be leveraged to execute against your user databases. Why not just develop this in the user database for ease? Several reasons:

  1. We want to develop this once and only once. We come from an environment where one SQL developer may be tasked with writing for six .net developers and we don’t want to introduce confusion, so just one connection string. The developers make the connection to the code database, and the stored procs that are executed in that code database will be executed against the user data that returns result sets accordingly. In short, all roads lead to Rome.
  2. If all our security is set for an empty database with just a bunch of code, if someone were to gain access uninvited, what do they have? And they have even less if that code is encrypted.
  3. It forces the DBA to use a three-part naming scheme of database name, schema name, and object name. While this may look like it takes more time referencing each object in the database, we have noticed the precision results in a dramatic decrease in troubleshooting time. You get used to the extra typing (especially since intelligence is your friend), and when you are developing enterprise solutions you don’t want to be called out for being bush league when a few more seconds make you into an enterprise surgical developer (right?).

You can choose any name you wish to call the database. At SQL Solutions Group, we develop several internal products against the database we are calling DBCatch, so we will be using that. You can also use the name DBCatch and we encourage that. Just as long as you do not sell anything under that name without discussing that with us and the copyright thereof. 

Create Database and Tables

We are not going to get too fancy with the database options; keeping it simple with this code:

CREATE DATABASE DBCatch;
GO

 

Great, the first task is complete. Next, we need to create a few tables. The first set of tables will be the code tables. I do not like creating different code tables for every function that comes up, so I create two tables that act as a kind of named value pair. That way, these two tables take care of almost all my code definition needs. The first table is the Code Group (this is the header), and the second is the Code (this is the detail).  Let’s just jump in and you will see what I am talking about once we populate the tables and you can see them in action.

USE DBCatch;
GO
 
CREATE TABLE [dbo].[TU_CodeGroup]
(
  [CodeGroup_ID]    [INT]  IDENTITY (1, 1)   NOT NULL
    CONSTRAINT [PK_CodeGroup_ID]   
    PRIMARY KEY CLUSTERED
 ,[Name]            [NVARCHAR](100)          NOT NULL
 ,[DESC]            [NVARCHAR](255)          NOT NULL
    CONSTRAINT DF_TU_CodeGroup_Desc
    DEFAULT ('Group Code')
 ,[Active]          [BIT]                    NOT NULL
    CONSTRAINT DF_TU_CodeGroup_Active
    DEFAULT ((1))
 ,[DateCreated]     [DATETIME]               NOT NULL
    CONSTRAINT DF_TU_CodeGroup_DateCreated
    DEFAULT (GETDATE())
 ,[DateModified]    [DATETIME]               NOT NULL
    CONSTRAINT DF_TU_CodeGroup_DateModified
    DEFAULT (GETDATE())
) ON [PRIMARY];
GO
 
 
CREATE TABLE [dbo].[TU_Code]
(
  [Code_ID]         [INT]  IDENTITY (1, 1)   NOT NULL 
    CONSTRAINT [PK_Code_ID]   
    PRIMARY KEY CLUSTERED
 ,[CodeGroup_ID]    [INT]                    NOT NULL
    CONSTRAINT [FK_TU_Code_CodeGroup_ID]
       FOREIGN KEY ([CodeGroup_ID]) 
    REFERENCES [TU_CodeGroup]([CodeGroup_ID])
 ,[Name]            [NVARCHAR](100)          NOT NULL
 ,[DESC]            [NVARCHAR](255)          NOT NULL
    CONSTRAINT [DF_TU_Code_Desc]
    DEFAULT 'Code'
 ,[Active]          [BIT]                    NOT NULL
    CONSTRAINT [DF_TU_Code_Active]
    DEFAULT 1
 ,[DateCreated]     [DATETIME]               NOT NULL
    CONSTRAINT [DF_TU_Code_DateCreated]
    DEFAULT GETDATE()
 ,[DateModified]    [DATETIME]               NOT NULL
    CONSTRAINT [DF_TU_Code_DateModified]
    DEFAULT GETDATE()
) ON [PRIMARY];
GO

 

Naming Scheme and Populating Tables

At SSG, we adhere to a specific naming scheme. I recommend anyone that develops code should adopt a naming scheme that works, for you and your company. The prefix TU_ above in front of the table names stands for user table. SSG does a lot of data dictionary work and when we describe our objects, we like them to be grouped nicely. This way we can describe each set of objects (like tables, views, procedures, etc.) in sets visually. Now that our code tables are in the DBCatch database, populate them with the data we need:

USE DBCatch;
GO
 
  SET IDENTITY_INSERT TU_CodeGroup ON;
 
 
INSERT [DBCATCH].[dbo].[TU_CodeGroup]
(      CodeGroup_ID, Name,                    [DESC])
SELECT 1,            'ERROR',                 'ERROR CODES';
 
 
  SET IDENTITY_INSERT TU_CodeGroup OFF;
 
 
  SET IDENTITY_INSERT TU_Code ON;
 
INSERT [DBCATCH].[dbo].[TU_Code]
(      Code_ID, CodeGroup_ID, Name,                    [DESC])
SELECT 100,     1,            'DEFAULT',               'UNKNOWN ERROR'
UNION
SELECT 101,     1,            'SQL',                   'INTERNAL SQL SERVER RELATED ERROR'
UNION
SELECT 102,     1,            'DBCATCH',               'DBCATCH RELATED ERROR'
UNION 
SELECT 103,     1,            'ESCLATION',             'ERROR THAT HAS BEEN ESCALATED TO A PARENT ERROR'
UNION   
SELECT 104,     1,            'MAINTENANCE',           'DATABASE MAINTENANCE RELATED ERROR';
 
 
  SET IDENTITY_INSERT TU_Code OFF;

 

You can see the base data we just populated, with the two select statements below. This will make more sense once we create the user-defined function and actually start using it in the template.

USE DBCatch;
GO
 
SELECT [CodeGroup_ID]
      ,[Name]
	  ,[DESC]
	  ,[Active]
	  ,[DateCreated]
	  ,[DateModified]
  FROM [DBCatch].[dbo].[TU_CodeGroup];
 
SELECT [Code_ID]
      ,[CodeGroup_ID]
      ,[Name]
	  ,[DESC]
	  ,[Active]
	  ,[DateCreated]
	  ,[DateModified]
  FROM [DBCatch].[dbo].[TU_Code];

 

Trapping Errors with the SQL Error Engine

Now, let’s dive into the user-defined scalar function. This retrieves the code IDs for us when we use them programmatically within our solution. The idea here is that we will be trapping any errors that come up by a try catch method inside our T_SQL code. Once we capture the error, we categorize it and define it ourselves. This way, the error is not cryptic and we can define it in a way that will point us to the issue. This cuts down our troubleshooting time.

First, we’ll create the function. Next, we will continue making this solution more customized.

USE DBCATCH;
GO
 
SET NOCOUNT ON;
 
IF EXISTS(SELECT 1
            FROM [INFORMATION_SCHEMA].[ROUTINES] (NOLOCK)
           WHERE [SPECIFIC_SCHEMA]   = 'dbo'
             AND [SPECIFIC_NAME]     = 'FSU_Code_GET')
BEGIN
  DROP FUNCTION [dbo].[FSU_Code_GET];
 
  PRINT '<<< DROPPED FUNCTION [dbo].[FSU_Code_GET] >>>';
END;
GO
 
CREATE FUNCTION [dbo].[FSU_Code_GET] 
(	
  @p_CodeGroup                       [NVARCHAR](100)
 ,@p_Code                            [NVARCHAR](100)
)
RETURNS INT
WITH ENCRYPTION
AS
BEGIN
 
/************************************************************************/
/* Name	  : FSU_Code_GET                                          */
/* Version      : 1.0                                                   */
/* Author       : Jared Kirkpatrick                                     */
/* Date	  : 2019-07-24                                            */
/* Description  : Get the value from the code table.                    */
/************************************************************************/
/* Date         : Version: Who: Description                             */
/************************************************************************/
/* 2019-05-24   : 1.0    : JSK: Initial Release.                        */
/************************************************************************/
 
  DECLARE @v_Return                  [INT];
 
  SELECT @v_Return                   = b.[Code_ID]
    FROM [DBCATCH].[dbo].[TU_CodeGroup] a 
    JOIN [DBCATCH].[dbo].[TU_Code]      b 
      ON a.[CodeGroup_ID]            = b.[CodeGroup_ID]
   WHERE a.[Name]                    = @p_CodeGroup
     AND b.[Name]                    = @p_Code;
 
  RETURN @v_Return;
 
END;
GO
 
IF OBJECT_ID('[dbo].[FSU_Code_GET]') IS NOT NULL
BEGIN
	PRINT '<<< CREATED FUNCTION [dbo].[FSU_Code_GET] >>>';
END;
ELSE 
BEGIN
	PRINT '<<< FAILED TO CREATE FUNCTION [dbo].[FSU_Code_GET] >>>';
END;
GO

 

To see what we have done here, let’s look at the two tables we first populated, TU_CodeGroup and TU_Code.

sql error engine tables

The TU_CodeGroup table has one record, and the value (actually, the {Name} field), is “ERROR”. In the TU_Code table we will be mostly using the code_id that corelates to the “DBCatch” value since we are aiming at categorizing errors we trap from DBCatch. If we want the code for DBCatch errors, we use our function which takes the value of the code group (Error) and the value of the code (“DBCatch”) and we retrieve it by leveraging the function as shown below which will give us the value 102…

USE DBCatch;
GO
 
SELECT dbo.[FSU_Code_GET]('Error', 'DBCatch');

 

Next, we create the table that will hold the error definition, and then we will populate those definitions.

USE DBCatch;
GO
 
CREATE TABLE [dbo].[TU_Error]
(
  [Error_ID]     [INT]  IDENTITY (1, 1)   NOT NULL 
    CONSTRAINT [PK_Error_ID]   
    PRIMARY KEY CLUSTERED
 ,[Type_ID]      [INT]                    NOT NULL
    CONSTRAINT [FK_TU_Error_Code_ID]
       FOREIGN KEY ([Type_ID]) 
    REFERENCES [TU_Code]([Code_ID])
 ,[Name]         [NVARCHAR](100)          NOT NULL
 ,[Severity]     [INT]                    NULL
 ,[STATE]        [INT]                    NULL
 ,[DESC]         [NVARCHAR](255)          NOT NULL
    CONSTRAINT [DF_TU_Error_Desc]
    DEFAULT 'Error'
 ,[Active]       [BIT]                    NOT NULL
    CONSTRAINT [DF_TU_Error_Active]
    DEFAULT 1
 ,[DateCreated]  [DATETIME]               NOT NULL
    CONSTRAINT [DF_TU_Error_DateCreated]
    DEFAULT GETDATE()
 ,[DateModified] [DATETIME]               NOT NULL
    CONSTRAINT [DF_TU_Error_DateModified]
    DEFAULT GETDATE()
) ON [PRIMARY];
 
USE DBCatch;
GO
 
  SET IDENTITY_INSERT TU_Error ON;
 
INSERT [DBCATCH].[dbo].[TU_Error]
(      Error_ID, [Type_ID], [Name],          [Severity], [STATE], [DESC])
SELECT 100000,   100,       'DEFAULT_DBCATCH',   '16',      '1',     'UNKNOWN ERROR IN DBCATCH'
UNION  
SELECT 100001,   102,       'DBCATCH_INSERT',    '16',      '1',     'ERROR INSERTING INTO A DBCATCH TABLE'
UNION  
SELECT 100002,   102,       'DBCATCH_UPDATE',    '16',      '1',     'ERROR UPDATING A DBCATCH TABLE'
UNION  
SELECT 100003,   102,       'DBCATCH_DELETE',    '16',      '1',     'ERROR DELETING FROM A DBCATCH TABLE'
UNION  
SELECT 100004,   102,       'DBCATCH_SELECT',    '16',      '1',     'ERROR DBCATCH DATA COLLECTOR (SELECTING INFO)';
 
 
 
  SET IDENTITY_INSERT TU_Error OFF;
GO

 

The final step for the definition is to add an error message in the SQL engine, but we are going to put a twist on this so it is not exactly straightforward. We set the message to be defined at runtime so that we can customize the error message and manipulate. This helps us understand what is happening.

When we get to the template that shows how this works, it will be clear. It is hard to describe what you cannot see, but we will have a demo at the end so that you can see it in action. Then the light bulbs will go off in that “a-ha!” moment.

Let’s make some error messages!

USE DBCatch;
GO
 
IF EXISTS(SELECT 1
            FROM [master].[sys].[messages] 
           WHERE [message_id] = 100000)
BEGIN
  EXEC [DBCATCH].[dbo].[SP_DROPMESSAGE] @msgnum = 100000;
END;  
 
EXEC [DBCATCH].[dbo].[SP_ADDMESSAGE] 
  @msgnum = 100000,
  @severity = 16,
  @msgtext = N'%s';
 
IF EXISTS(SELECT 1
            FROM [master].[sys].[messages] 
           WHERE [message_id] = 100001)
BEGIN
  EXEC [DBCATCH].[dbo].[SP_DROPMESSAGE] @msgnum = 100001;
END;  
 
EXEC [DBCATCH].[dbo].[SP_ADDMESSAGE] 
  @msgnum = 100001,
  @severity = 16,
  @msgtext = N'%s';
 
IF EXISTS(SELECT 1
            FROM [master].[sys].[messages] 
           WHERE [message_id] = 100002)
BEGIN
  EXEC [DBCATCH].[dbo].[SP_DROPMESSAGE] @msgnum = 100002;
END;  
 
EXEC [DBCATCH].[dbo].[SP_ADDMESSAGE] 
  @msgnum = 100002,
  @severity = 16,
  @msgtext = N'%s'; 
 
IF EXISTS