Apr 16, 2010

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, 

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())


--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)
    IF @in_SearchType = 'MAX LEN' AND (LEN(@in_SearchText) <= CAST(@RuleParameter AS INT))
    IF(@in_SearchType = 'MIN LEN' AND LEN(@in_SearchText) >= CAST(@RuleParameter AS INT))
    IF(@in_SearchType = 'LIKE')
        IF(@in_IsCaseSensitive = 0 AND @in_SearchText LIKE CAST(@RuleParameter AS VARCHAR(256)))
        IF(@in_IsCaseSensitive = 1 AND @in_SearchText COLLATE Latin1_General_BIN LIKE 
CAST(@RuleParameter AS VARCHAR(256)) COLLATE Latin1_General_BIN)
    IF(@in_SearchType = 'NOT LIKE' AND @in_SearchText NOT LIKE CAST(@RuleParameter AS VARCHAR(256)))

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!!


Wilfred van Dijk said...

Great article. Tip: I changed the datatype for procname to SYSNAME and made the RuleID an identity.
I implemented some rules to determine if depreciated features are being used in stored procedures (which is also an interesting 'feature' of this script)

Omnibuzz said...

Good to hear that the script is being put to use. And both your suggestions are valid. Not sure why I didn't think of 'em. Will update the script.
If you can post the rules that you added, I can add them to the script (if its fine with you)

Anonymous said...

Awesome idea! Wouldn't this be an intellectual property of the company/client you work for? Just curious :) I guess its the joy of sharing great ideas with the world that drives you! Keep up the good work bro.
- you know who :)

Eran Smith said...

Nice collection. One thing that i want to say that you have to use comment's on so that it may easy to understand the function of that line.

.net Obfuscators

Yuriy Rozhok said...

Probably this could be helpful for you: similar logic can be implemented with Policy Management feature in SQL 2008

Arun Mariappan K said...

Nice post. Many Thanks

Post a Comment