Motivation

At some point in the carrer, we have come across the problem of hard-coded values in SELECT or WHERE clauses.  And we all agree that these hardcoded values must be parametrised. This bad habit usually backfires when we need to troubleshoot a query. These hardcoded values are usually a business role baked in the code. The question I frequently ask myself is, how to manage business logic and business rules for better transparency, code reusability, and faster troubleshooting.

Business Logic

Managing your business logic is not an easy task. Storing it outside of the T-SQL queries will help you build a sustainable system. If your code is on the transformation, business, presentation, or any other layer, it should be kept in the same place as the parameters. In this article, I will guide you through a tiny framework, that will help you achieve this.

But first, let us differentiate the types of rules that represent business logic. These rules are:

• Bulk business rules -  rules that usually create a simple operation on a large dataset before further ETL/ELT/analytical tasks; e.g.: "recalculate product tax on all invoices before given date". Usually affects the complete data warehouse, departments, and datasets.
• Business logic in SELECT clause - rules that follow a smaller subset of data and directly reflect end users' idea of data transformation or data visualisation. Usually affects a smaller group of people or department.
• Business logic in WHERE clause - rules that have an effect on subset selection and have huge potential on data being selected correctly. Usually affects everyone in the organisation and all data layers.
• Business rules for error coding - rules that represent wrappers for handling errors, faults, and missing data must also be part of this framework.

I have created this classification based on the following criteria.

1. number of users affected,
2. importance of the data for the core business,
3. level of data abstraction and curation, and
4. type of usage - internal or external.

Tiny framework for Managing Business Rules

The framework consists of three T-SQL Tables. We will use the first table to store all the queries, the second one to store parameters, and the third one the executions. Along with tables, you will  also need two procedures for the framework. With the first procedure you will convert the parametrised query into the normal query and the second procedure will help you track changes on parameters.

Framework tables

The framework tables help you store the parametrised values and queries for automated creation. You will also store parameter version for tracking changes.

Table for storing parametrized queries

Storing the queries is the most important part of understanding this framework. Start with storing your queries in this table. In this way, you will start keeping business logic out of the T-SQL code.

IF object_id ('BusinessRules_Query', 'U') IS NOT  NULL
DROP TABLE dbo.BusinessRules_Query
CREATE TABLE dbo.BusinessRules_Query
(
 id INT IDENTITY(1,1) NOT NULL
,query_type VARCHAR(15) NOT NULL -- Procedure, Function, View
,query_object_name VARCHAR(200) NULL -- enter the procedure/function/view name
,query_id INT NOT NULL  -- Object_id() 
,query_text NVARCHAR(MAX)  NULL  -- query  
,query_text_withParameters NVARCHAR(MAX) NOT NULL  -- query with parameters
,user_created VARCHAR(50) NOT NULL DEFAULT (suser_name())
,date_created DATETIME NOT NULL DEFAULT (GETDATE())
,rule_version INT DEFAULT(1)
,CONSTRAINT PK_BussinesRulesQuery_QueryID_Version
               PRIMARY KEY CLUSTERED (query_id, Rule_version)
               WITH (IGNORE_DUP_KEY = OFF)
);

Once you create a table, you can insert this sample query. This sample  will show you, how to insert parametrised query into the table.

 INSERT INTO dbo.BusinessRules_Query ([query_type], [query_object_name], [query_id], [query_text], [query_text_withParameters])
 SELECT 'Procedure','dbo.sp_SampleQuery1', 10203
 ,NULL
,
'CREATE PROCEDURE dbo.sp_SampleQuery1
AS
SELECT * 
FROM
(
SELECT 
name
,number
,$selectkey1
FROM 
master.dbo.spt_values
WHERE

$wherekey1
AND status = 0
) AS x
join msdb.dbo.MSdatatype_mappings as m
ON m.dbms_name =  x.dbms_name
'

The parameters will be named with the $ prefix and enumerated.

The query above holds the parameter "$selectKey1".  You will later create this parameter in the dbo.BusinessRules_Parameters table. There is no particular naming behind the parameters, but for immediate understanding, I propose the following naming convention:

• $selectKey[1 .. n]
• $whereKey[1..n]

In addition, each parameter is enumerated, for easier reading and maintenance.

Storing Parameters

Storing parameters in table dbo.BusinessRules_Parameters is the corresponding part to the query that is stored in the Business Query table. In this table you will store the values, that used to be hard-coded in the query.

CREATE TABLE dbo.BusinessRules_Parameters
(
 id INT IDENTITY(1,1) NOT NULL
,query_id INT NOT NULL  
,query_parameter_Description VARCHAR(500) 
,query_parameter_tableRelated VARCHAR(500) --  more tables separated with semi-colon ";"
,query_key VARCHAR(20) -- eg.: $selectkey1 $wherekey1
,query_value NVARCHAR(MAX) -- query part
-- housekeeping
,user_created VARCHAR(50) NOT NULL DEFAULT (suser_name())
,date_created DATETIME NOT NULL DEFAULT (GETDATE())
,parameter_version INT DEFAULT(1)
,parameter_active TINYINT DEFAULT(1) -- 1-is active; 0 - is not active
,CONSTRAINT PK_BussinesRulesParameters_QueryID_queryKey_Version
               PRIMARY KEY CLUSTERED (query_id, query_key, parameter_version)
               WITH (IGNORE_DUP_KEY = OFF)
)

After table creation, you will insert the parameter itself.

The Parameter name "$selectkey1" is corresponding to your query. You will add the  section of the code, that used to be hard-coded in this table. The values will still be hardcoded, but you will have them in one table, accompanied with table name, description, user and date. This will give you better visibility and easy curation.

  INSERT INTO dbo.BusinessRules_Parameters ([query_id], [query_parameter_Description], [query_parameter_tableRelated], [query_key], [query_value])
 SELECT 10203
 ,'CASE Statement to determine if ORACLE or SYBASE type'
 ,'master.dbo.spt_values'
 ,'$selectkey1'
 ,'CASE WHEN name like ''DB %'' THEN ''ORACLE'' ELSE ''SYBASE'' END As dbms_name'

Furthermore, not only that the framework will provide the visibility of hard-coded values in the T-SQL clauses and predicates, but will also give you data lineage. Storing the table and column name, will boost the transparency and understanding of the business logic.

Table for executions

In this table you will  store the query versions and select which rules will be executed. You will achieve this by  enabling or disabling each of the rules.

IF object_id ('BusinessRules_Executions', 'U') IS NOT  NULL
DROP TABLE dbo.BusinessRules_Executions
CREATE TABLE dbo.BusinessRules_Executions
(
 id INT IDENTITY(1,1) NOT NULL
,query_id INT NOT NULL  
,query_execution TINYINT NOT NULL DEFAULT(0) -- 0 - rule is on; 1 - rule is off
,user_created VARCHAR(50) NOT NULL DEFAULT (suser_name())
,date_created DATETIME NOT NULL DEFAULT (GETDATE())
,CONSTRAINT PK_BussinesRulesExecution_QueryID PRIMARY KEY CLUSTERED (query_id)
);

This table will be populated with thought the stored procedure dbo.sp_Create_Objects, as every time the procedure will be called, the executions will be logged.

Framework procedures

The framework will be operated using two procedures. As we have previously mentioned, the first one will be used for creating SQL Server objects (Functions, View, Procedures, Queries) from Business Query and Business Parameters tables and the second for updating parameters.

The main script will create (or alter) the object based on the parametrised query. You will pass the query ID  (lookup on table dbo.BusinessRules_Query is created) as an input parameter and execute the procedure.

CREATE OR ALTER PROCEDURE dbo.sp_Create_ScriptObjects
(
@Query_ID INT 
,@ScriptObject TINYINT = 1 -- 1 default value; returns script; set to 0, generates object!
)
AS
BEGIN
DECLARE @i INT = 1
DECLARE @tip CHAR(1)= (SELECT query_type FROM dbo.BusinessRules_Query WHERE query_id = @query_ID)
DECLARE @ime VARCHAR(200)= (SELECT query_object_name FROM dbo.BusinessRules_Query WHERE query_id = @query_ID)
DECLARE @nof_params INT = (SELECT count(*) FROM dbo.BusinessRules_Parameters as P JOIN  dbo.BusinessRules_Query as R ON P.Query_ID = R.query_id WHERE R.query_id = @query_ID)

IF OBJECT_ID('tempdb..#temp123','U') IS NOT NULL
DROP TABLE #temp123
SELECT 
 row_number() over (ORDER BY (SELECT 1)) as RN
,query_id
,[query_key]
,[query_value]
INTO #temp123
FROM dbo.Bus_Rules_parameters
WHERE [query_id] = @query_id
ORDER BY ID ASC
DECLARE @sqlUkaz NVARCHAR(MAX) = (SELECT [query_text_withParameters] FROM dbo.BusinessRules_Query WHERE query_id = @Query_ID)
    DECLARE @ObjectType NVARCHAR(30) = (SELECT [query_type] FROM dbo.BusinessRules_Query WHERE query_id = @Query_ID)
WHILE (@i <= @nof_params)
BEGIN
DECLARE @param_key VARCHAR(100) = (SELECT [query_key] FROM #temp123 WHERE rn = @i)
DECLARE @param_value VARCHAR(MAX) = (SELECT [query_value] FROM #temp123 WHERE rn = @i)
SET @sqlUkaz = (SELECT REPLACE( @sqlUkaz, @param_key, @param_value))
SET @i = @i +1
END
    IF (@ScriptObject = 1) SELECT @sqlUkaz
    IF (@ScriptObject = 0)
    BEGIN
        DECLARE @DropSQL NVARCHAR(100) = 'DROP ' + @ObjectType + ' IF EXISTS ' + @ime
        exec sp_executesql @DropSQL
        exec sp_executesql @sqlUkaz
    END
   IF (@ScriptObject NOT IN (1,0))
BEGIN
SELECT 'Wrong parameter'
END
END;
GO

Each object will be available for you after the procedure finishes. The procedure takes corresponding query (Query ID) and looks for the parameters, that are assigned to this query. Further on, the procedure replaces the parameters with actual code and executes it.

You also want to keep tracking changes. With this procedure, you will be able to store the changes. And you will also be able to  switch each parameter on or off.

CREATE OR ALTER PROCEDURE dbo.sp_Update_Parameters
(
 @Query_ID INT
,@Query_key VARCHAR(20)
,@new_query_value NVARCHAR(MAX) = NULL
,@new_query_parameter_Description VARCHAR(500) = NULL
,@new_query_table_related VARCHAR(500) = NULL
,@is_enabled TINYINT = 1
)
/*
Usage:
EXEC dbo.sp_Update_Parameters 
@query_id = 10203
,@Query_key = '$wherekey2'
,@new_query_value = ' '
,@new_query_parameter_Description = ''
,@new_query_table_related = ''
,@is_enabled = 0
*/AS
BEGIN

-------------------------------------
-- Only when disabling the parameter!
-------------------------------------
IF (@is_enabled = 0)
BEGIN
DECLARE @pam_ver0 INT = (SELECT max(parameter_version) from BusinessRules_Parameters where [query_id] = @query_id AND query_key =  @Query_key)
UPDATE dbo.BusinessRules_Parameters 
SET parameter_active = 0
WHERE
query_id = @query_id
and query_key = @Query_key
and parameter_version = @pam_ver0
-- Update procedure and replace the parameter with "1=1"
UPDATE dbo.BusinessRules_Query
SET query_text_withParameters = REPLACE(query_text_withParameters, @Query_key, ' 1=1 ' )
WHERE 
query_id = @query_id
END
-------------------------------------
-- When parameter exists & is updated
-------------------------------------
-- INSERT NEW VALUE FOR PARAMETER
IF (@is_enabled = 1)
BEGIN

DECLARE @pam_ver1 INT = (SELECT max(parameter_version) from BusinessRules_Parameters where [query_id] = @query_id AND query_key =  @Query_key)

UPDATE dbo.BusinessRules_Parameters 
SET @is_enabled = 0
WHERE
query_id = @query_id
AND query_key = @Query_key
AND parameter_version = @pam_ver1

 INSERT INTO dbo.BusinessRules_Parameters ([query_id], [query_parameter_Description], [query_parameter_tableRelated], [query_key], [query_value], parameter_version, parameter_active)
 SELECT 
 @query_id AS query_id
,@new_query_parameter_Description AS query_parameter_Description
,@new_query_table_related AS query_parameter_tableRelated
,@Query_key  AS query_key
,@new_query_value AS query_value
,@pam_ver1 + 1 AS parameter_version
,@is_enabled AS Parameter_active
END
--Run CREATE procedure
EXEC dbo.sp_Create_ScriptObjects
@query_id = @query_id
END;
GO

Using a framework with an everyday scenario

Now that you understanding the idea and the components of the framework, it is time to see, how to start using a framework in your existing environment.

Suppose you have a view with some hardcoded values in the query. Now you want to have a little bit more visibility over these parameters. Take, for example, the following the CREATE statement.

CREATE VIEW dbo.vw_SampleQuery2
AS
SELECT 
 t.TABLE_CATALOG
,t.table_name
,t.TABLE_SCHEMA
,c.COLUMN_NAME
,c.ORDINAL_POSITION
,c.IS_NULLABLE
,c.DATA_TYPE
,c.NUMERIC_PRECISION
,(c.NUMERIC_PRECISION * c.ORDINAL_POSITION) AS Some_calucation_for_Size
FROM
MASTER.[INFORMATION_SCHEMA].[COLUMNS] AS c
JOIN MASTER.[INFORMATION_SCHEMA].[TABLES] AS t
ON t.TABLE_NAME = c.TABLE_NAME
AND t.TABLE_SCHEMA = c.TABLE_SCHEMA
AND t.TABLE_CATALOG = c.TABLE_CATALOG
WHERE
c.DATA_TYPE IN ('int','smallint','bigint','tinyint')
AND c.ORDINAL_POSITION <= 10 ;
GO

Decide how you want to parametrise your query.  In my case, I have decided to take the one column from the select list out.

(c.NUMERIC_PRECISION * c.ORDINAL_POSITION) AS Some_calucation_for_Size

And the two conditions from the WHERE clause.

-- first
c.DATA_TYPE IN ('int','smallint','bigint','tinyint')
-- second
c.ORDINAL_POSITION <= 10

Now you will insert query with parameters into dbo.BusinessRules_Query table.

 INSERT INTO dbo.BusinessRules_Query ([query_type], [query_object_name], [query_id], [query_text], [query_text_withParameters])
 SELECT 'View','dbo.vw_SampleQuery2', 10200
 ,NULL
,
'CREATE VIEW dbo.vw_SampleQuery2
AS
SELECT 
 t.TABLE_CATALOG
,t.table_name
,t.TABLE_SCHEMA
,c.COLUMN_NAME
,c.ORDINAL_POSITION
,c.IS_NULLABLE
,c.DATA_TYPE
,c.NUMERIC_PRECISION
,$selectkey1
FROM
MASTER.[INFORMATION_SCHEMA].[COLUMNS] AS c
JOIN MASTER.[INFORMATION_SCHEMA].[TABLES] AS t
ON t.TABLE_NAME = c.TABLE_NAME
AND t.TABLE_SCHEMA = c.TABLE_SCHEMA
AND t.TABLE_CATALOG = c.TABLE_CATALOG
WHERE
$wherekey1
AND $wherekey2'

And for this same query, you will insert the corresponding parameters.

  INSERT INTO dbo.BusinessRules_Parameters ([query_id], [query_parameter_Description], [query_parameter_tableRelated], [query_key], [query_value])
  SELECT 10200
 ,'Calculating some random size as business rule'
 ,'MASTER.[INFORMATION_SCHEMA].[COLUMNS]'
 ,'$selectkey1'
 ,'(c.NUMERIC_PRECISION * c.ORDINAL_POSITION) AS Some_calucation_for_Size'
   UNION ALL
  SELECT 10200
 ,'Selecting only numerical data types'
 ,'MASTER.[INFORMATION_SCHEMA].[COLUMNS]'
 ,'$wherekey1'
 ,'c.DATA_TYPE IN (''int'',''smallint'',''bigint'',''tinyint'')'
    UNION ALL
  SELECT 10200
 ,'Omitting the number of columns per table'
 ,'MASTER.[INFORMATION_SCHEMA].[COLUMNS]'
 ,'$wherekey2'
 ,'c.ORDINAL_POSITION <= 10'

Note that the Key connecting both tables is the query_ID, which is set arbitrary as a number.

Once you have populated the table with the values, go ahead and run the create object procedure:

EXEC dbo.sp_Create_ScriptObjects 
@query_id = 10200;

And lastly, when you decide you want to change the parameters, there are two ways. You can update the same statement in the table dbo.BusinessRules_parameters or you can run the update the procedure.

EXEC dbo.sp_Update_Parameters 
@query_id = 10200
,@Query_key = '$wherekey2'
,@new_query_value = ' '
,@new_query_parameter_Description = '
,@new_query_table_related = '
,@is_enabled = 1

If you decide to schedule the creation of the object(s),  you will simply add the query_id in the dbo.BusinessRules_execution table.

INSERT INTO dbo.BusinessRules_Executions (query_id, Query_execution)
SELECT 
 10200, 0

I have created a SQL Server job to schedule the object creations.

--- Creating SQL Server Job:
USE msdb ;  
GO  
EXEC dbo.sp_add_job  
    @job_name = N'Weekly Object Creations' ;  
GO  
EXEC sp_add_jobstep  
    @job_name = N'Weekly Object Creations',  
    @step_name = N'Create objects from BusinessRules queries',  
    @subsystem = N'TSQL',  
    @command = N'

DECLARE @var1 INT
DECLARE @cur CURSOR
SET @cur = CURSOR STATIC FOR
    SELECT query_id FROM dbo.BusinessRules_Executions WHERE query_execution = 0

OPEN @cur
WHILE 1 = 1
BEGIN
     FETCH @cur INTO @var1
     IF @@fetch_status <> 0
        BREAK
    EXEC dbo.sp_Create_ScriptObjects 
@query_id = @var1
,@ScriptObject = 0
END
',   
    @retry_attempts = 5,  
    @retry_interval = 5 ;  
GO  
EXEC dbo.sp_add_schedule  
    @schedule_name = N'RunOnce',  
    @freq_type = 1,  
    @active_start_time = 003000 ;  
USE msdb ;  
GO  
EXEC sp_attach_schedule  
   @job_name = N'Weekly Object Creations',  
   @schedule_name = N'RunOnce';  
GO  
EXEC dbo.sp_add_jobserver  
    @job_name = N'Weekly Object Creations';  
GO

If your SQL Server objects do not change that often, you can schedule this framework as part of your maintenance scripts. And since it's once per day/week operation, I haven't added any extra complexity to scheduling tasks. But you can always extend the framework.

Conclusion

The framework is written in T-SQL  and you can use it with Microsoft SQL Server or Microsoft Azure SQL. Therefore, it is made easy to adapt for you and you can implement or even extend it to your business needs. You will gain clarity, governance and data trust. And to some extent, you will start raising the awareness of the danger of storing business logic in the code.

You can track future updates on the Github repository.