Feature: SQL Business Rule Validation

Our procs almost always operate on certain widely referenced tables that are filtered with a where clause based on some common variables. For example if we are refering to data in certain stage tables it is almost always WHERE @load_id = n AND/OR @client_id = m because the tables contain data for multiple loads and multiple clients. It is usually vital that we include these variables (which are used throughout the system) otherwise we return data for more than one load, or we update records for the wrong client. This is pretty standard behaviour of course.

It would be great if there was a tool to specify some simple rules to automatically check/validate that we have done this, as it can be easy to miss or accidentally delete where clauses in a thousand line proc, especially if there are 5 or 10 other where clauses in the statements. For example one rule could be:

IF a SQL statement refers to a table called %stage_load_data% [where % = wildcard, so it could cover multiple similarly named tables]
THEN it must also reference a variable called @load_id in the where clause
AND it must also reference a variable called @client_id in the where clause

Could this be done with simple string manipulation on each parsed statement? Any SQL statements not meeting that rule could be highlighted as a possible risk. For us, and I'm guessing for others, this would help mitigate potentially nasty bugs, and there are probably many other rules that could be applied and validated.

Hi, that is not currently possible.

SQL Assistant is not a tool for validating custom business rules. It is a tool for generating code, advanced SQL Intellisense, validating code syntax, refactoring database code and objects, formatting SQL, running SQL, helping with SQL code entry, etc..., etc... You can also use it to develop custom code snippets for super fast code entry and code automation. The templates for such code snippets can be designed to include references to @client_id and other auto-generated variables, SQL conditions and code structures specific to your business application.