A lightweight framework for managing your business rules and logic in Transact SQL for on-prem Microsoft SQL Server and Azure SQL Database, Azure SQL Server, or Azure SQL MI.
Have you ever found yourself with T-SQL queries and hard-coded values? In SELECT or in WHERE clauses. The framework will help you with values parametrisation and an easier way to manage the rules.
- Store your hard-coded values in SQL tables
- Parametrise your business rules and business logic
- Keeping versions of all parameters
- Leverage data transparency
- Learn interesting facts about your data
The easiest way to get started is with your open code folder. Go to github/code, and run the Install.sql in your SQL Server database.
There is a showcase sample file prepared to get started with the framework. Go to github/code, and run the Showcase.sql in your SQL Server database.
DROP PROCEDURE IF EXISTS dbo.sp_SampleQuery1;
GO
-- INSERT PROCEDURE in TABLE with defined two parameters!
-- $SelectKey1
-- $WhereKey1
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..spt_values
WHERE
$wherekey1
AND status = 0
) AS x
join msdb..MSdatatype_mappings as m
ON m.dbms_name = x.dbms_name
'
--- INSERT Parameters
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..spt_values'
,'$selectkey1'
,'CASE WHEN name like ''DB %'' THEN ''ORACLE'' ELSE ''SYBASE'' END As dbms_name'
UNION ALL
SELECT 10203
,'Define the type of names query will be returning'
,'master..spt_values'
,'$wherekey1'
,'[type] IN (''DBR'',''DC'',''O9T'')'
--- Run Object Creation
EXEC dbo.sp_Create_ScriptObjects
@query_id = 10203
,@scriptObject = 0;
--- UPDATE Parameters; We push change to WHERE PARAMETER
EXEC dbo.sp_Update_Parameters
@query_id = 10203
,@Query_key = '$wherekey1'
,@new_query_value = ' [type] IN (''DBR'',''DC'') '
,@new_query_parameter_Description = 'Define the type of names query will be returning'
,@new_query_table_related = 'master..spt_values'
,@is_enabled = 1
The parametrisation is the core concept of the framework. Without hardcoding the values and attributes to your T-SQL Code, you can store the parameters separately and operate them without tedious code dive.
A simple T-SQL procedure
CREATE PROCEDURE dbo.sp_SampleQuery1
AS
SELECT * FROM
(
SELECT
name
,number
,CASE WHEN name like 'DB %' THEN 'ORACLE' ELSE 'SYBASE' END As dbms_name
FROM
master..spt_values
WHERE
[type] IN ('DBR','DC','O9T')
AND status = 0
) AS x
JOIN msdb..MSdatatype_mappings AS m
ON m.dbms_name = x.dbms_name
;
GO
is converted to parametrised query:
CREATE PROCEDURE dbo.sp_SampleQuery1
AS
SELECT * FROM
(
SELECT
name
,number
,$selectkey1
FROM
master..spt_values
WHERE
$wherekey1
AND status = 0
) AS x
JOIN msdb..MSdatatype_mappings AS m
ON m.dbms_name = x.dbms_name
And the values are separately inserted into table dbo.BusinessRules_Parameters
with all values:
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..spt_values'
,'$selectkey1'
,'CASE WHEN name like ''DB %'' THEN ''ORACLE'' ELSE ''SYBASE'' END As dbms_name'
The framework works with any of the following versions:
- Microsoft SQL Server database (works on all versions and editions)
- Azure SQL Database
- Azure SQL Server
- Azure SQL MI
and
- queries, views, functions or procedures with hard-coded values ๐
- lost documentation and angry data engineers ๐
The framework should be fun and light, not stern and stressful. Using the framework without explaining to everyone why you shouldn't use it for better results. And it should not be a scary mammoth - if anything, the better choice around.
We believe that parametrisation is the best philosophy.
If you are interested in collaborating project, feel free to clone or fork the repository. Read about the collaboration github/collaborate.
git clone https://github.com/tomaztk/Business-rules-for-T-SQL.git
- 0.1.0 - Initial script (August 22nd, 2022)
Framework is under the MIT license. See the LICENSE for more information.
- SQLServer Central Article published September 2nd, 2022