Custom SQL Stored Procedure Best Practices Analyzer - SQL Cop, Maybe?

Recently, I moved to Seattle as a Data Architect for a product our company was developing. I also started taking up most of the DBX roles in that project.

Though our architecture is predominantly CRUD based and we have our in-house CRUD proc generator, some business logic invariably seeps into the SPs. And we had let it to the discretion of the developers on whether to do the processing at Data or the Business Layer. I thought it would make sense to keep a rudimentary check on what logic was going into the SP and make sure our developers don't misuse the liberty and continue to follow the guidelines defined.

After a futile search for a tool like FXCop for SQL Server, I thought it would be fun to build a simple Stored Procedure validation tool that will solve the purpose. What follows is the process on how I went about building the tool. The code is given at the end of this post. Feel free to use it, extend it and share it.

To start with, I wanted the validation to be done on 4 entities
1. Proc Name
2. Proc Definition
3. Input parameters
4. Output parameters

I decided to use the EVENTDATA generated by the DDL trigger to do the validations on the proc name and the definition. And I plan to query the sys.parameters table to do any validations on the proc parameters.

We will be doing 3 types of validations on the entities:
1. Verify that entity contains a specific text
2. Verify that entity doesn't contain a specific text
3. For names, verify the length of the value is within a min and max length

With the primary requirements stated, lets define the other constraints and requirements:
1. Version: SQL Server 2005.
2. The validation rules have to run every time a SP is compiled to check for best practices.
3. Existing validation rules can be updated and new ones added.
4. The administrator should be able to set whether non-compliance to a validations will fail the compile or not (IsCriticalValidation).
5. Log all compiled non-compliance for reference.
6. There should be an option for the admin to over-ride any validation for any SP
7. CLR is disabled in the server (Just didn't want the solution to mandate CLR)

The above requirements compelled me to follow this design approach:
1. Use a DDL Trigger (Requirements: 1,2)
2. Have a table to store validation rules (Reqirements: 2,3,4)
3. Have a table for logging non-compliance (Requirement: 5)
4. Have a table for storing SP exceptions (Requirement: 6
5. Everything in T-SQL (Requirement: 7)

Lets start with building the tables:

-- Table for proc validation rules
CREATE TABLE dbo.__SYS_ProcValidationRules(
      RuleID smallint NOT NULL,
      RuleDescription nvarchar(256) NOT NULL,
      IsActive bit NOT NULL,
      SearchableEntity nvarchar(32) NULL,
      SearchType nvarchar(32) NULL,
      IsCaseSensitive bit NULL,
      RuleParameter sql_variant NULL,
      IsCriticalValidation bit NOT NULL, 
      PRIMARY KEY CLUSTERED (RuleID ASC)
)

Now, I added a few best practices that you want your developers to follow into this rules table. The table now looks like this:


Now we create the other tables for the sake of completeness. We will not be filling any data now.

--Table to hold SP exceptions
CREATE TABLE dbo.__SYS_ProcExceptions(
      SchemaName nvarchar(256) NOT NULL DEFAULT ('dbo'),
      ProcName nvarchar(1000) NOT NULL,
      ExceptionReason nvarchar(2000) NULL,
      ExceptionRuleID smallint NULL,
      CreateDate datetime NOT NULL DEFAULT (getdate()),
      CreateUser nvarchar(100) NOT NULL DEFAULT (suser_sname())
)

GO

--Table to maintain log on non-compliant SPs created
CREATE TABLE dbo.__SYS_ProcNonCompliance(
      SchemaName nvarchar(256) NOT NULL DEFAULT ('dbo'),
      ProcName nvarchar(1000) NOT NULL,
      RuleDescription nvarchar(256) NOT NULL,
      IsException bit NOT NULL,
      CreateDate datetime NOT NULL DEFAULT (getdate())
)

We now create the function that will accept the rule and entity value and returns whether the rule passed or not.

-- Function to evaluate any rule
CREATE FUNCTION dbo.__SYS_ValidateRule(@in_SearchText varchar(MAX), 
@in_SearchType varchar(32),
@in_IsCaseSensitive bit, 
@RuleParameter sql_Variant)
RETURNS BIT
WITH ENCRYPTION
AS
BEGIN
    IF @in_SearchType = 'MAX LEN' AND (LEN(@in_SearchText) <= CAST(@RuleParameter AS INT))
        RETURN(1);
    IF(@in_SearchType = 'MIN LEN' AND LEN(@in_SearchText) >= CAST(@RuleParameter AS INT))
        RETURN(1);
    IF(@in_SearchType = 'LIKE')
    BEGIN
        IF(@in_IsCaseSensitive = 0 AND @in_SearchText LIKE CAST(@RuleParameter AS VARCHAR(256)))
        RETURN(1);
        IF(@in_IsCaseSensitive = 1 AND @in_SearchText COLLATE Latin1_General_BIN LIKE 
CAST(@RuleParameter AS VARCHAR(256)) COLLATE Latin1_General_BIN)
        RETURN(1);
    END
    IF(@in_SearchType = 'NOT LIKE' AND @in_SearchText NOT LIKE CAST(@RuleParameter AS VARCHAR(256)))
        RETURN(1);
    
    RETURN(0);
END

The only one thats remaining now is the Database Trigger for create and alter procedure. You can get the definition of that trigger from the link below.

This link holds all the necessary scripts. After running the scripts, here is what happens when I try to compile a sub-standard procedure.



Suggestions and feedback welcome!!