Creating a SQL Rule Engine Table (Validation Conditions Executed Dynamically)

A SQL-based Rule Engine allows you to define validation logic as data instead of hard-coded procedures. This is extremely useful in ERP, CRM, WMS, finance, banking, healthcare, and dynamic form engines where business rules change frequently but deployments are slow or expensive.

This article explains how to design a dynamic rule engine stored entirely in SQL with:

Rule definition tables
Condition groups
Dynamic SQL evaluation
Parameter injection
Runtime orchestration
Exception logging
Flowcharts + architecture diagrams

1. Core Problem

Business logic changes often:

“If Quantity > Stock → throw error”
“If Customer.CreditLimit < Order.Total → fail”
“If Item.Category = ‘Hazardous’ → require special approval”
“If Vendor is blocked → stop PO creation”

Traditionally:
Developers change code → deploy → test → release.

Better approach:
Store rules in tables → interpret them dynamically → no deployments needed.

2. High-Level Architecture

                   ┌────────────────────────┐
                   │   Application Layer     │
                   │ (API, UI, Microservices)│
                   └─────────────┬───────────┘
                                 │
                     ┌───────────┴────────────┐
                     │    Rule Engine Layer    │
                     │  (SQL Stored Procedure) │
                     └───────┬─────────────────┘
                             │
            ┌────────────────┴────────────────────┐
            │      Rule Definition Tables         │
            │   - Rules                           │
            │   - RuleConditions                  │
            │   - RuleConditionGroups             │
            │   - RuleSeverity                    │
            │   - EntityFieldCatalog              │
            └─────────────────────────────────────┘

3. Rule Engine Table Design

Below is a battle-tested schema used in real enterprise systems.

3.1 Rule Table (Rule Master)

Stores the rule metadata.

CREATE TABLE RuleMaster (
    RuleId INT IDENTITY PRIMARY KEY,
    RuleName VARCHAR(200),
    EntityName VARCHAR(200),         -- “SalesOrder”, “Stockline”, etc.
    IsActive BIT DEFAULT 1,
    Severity VARCHAR(30),            -- "Error", "Warning", "Info"
    ErrorMessage VARCHAR(500),
    CreatedDate DATETIME2 DEFAULT SYSDATETIME()
);

Examples:

“SO_Total_Validation”
“Stock_Availability_Check”
“PO_Block_Vendor_Validation”

3.2 Rule Condition Groups

Groups allow AND/OR nesting.

CREATE TABLE RuleConditionGroup (
    GroupId INT IDENTITY PRIMARY KEY,
    RuleId INT FOREIGN KEY REFERENCES RuleMaster(RuleId),
    GroupOperator VARCHAR(5) CHECK (GroupOperator IN ('AND','OR'))
);

Example:
Group 1: Quantity > 0 AND Price > 0
Group 2: Customer.CreditLimit < Order.Total

3.3 Rule Conditions

Each group contains conditions stored as rows, not code.

CREATE TABLE RuleCondition (
    ConditionId INT IDENTITY PRIMARY KEY,
    GroupId INT FOREIGN KEY REFERENCES RuleConditionGroup(GroupId),
    LeftOperand VARCHAR(200),        -- Field name, JSON path, SQL expression
    Operator VARCHAR(10),            -- =, <>, >, <, >=, LIKE, IN, BETWEEN, etc.
    RightOperand VARCHAR(200),       -- Literal value or field-to-field
    DataType VARCHAR(20),            -- Int, Decimal, Date, NVarchar
);

Example rows:

LeftOperand	Operator	RightOperand	DataType
OrderTotal	>	CreditLimit	Decimal
Status	<>	'Closed'	NVARCHAR
Quantity	<=	StockQty	Int

4. Rule Orchestration Table (Optional but Recommended)

Allows mapping rules to events:

CREATE TABLE RuleEventMapping (
    Id INT IDENTITY PRIMARY KEY,
    RuleId INT FOREIGN KEY REFERENCES RuleMaster(RuleId),
    EventName VARCHAR(200)           -- "OnCreate", "OnUpdate", "OnSubmit"
);

5. Flowchart: Rule Execution Workflow

                   ┌────────────────────────────┐
                   │ START                       │
                   └───────────────┬─────────────┘
                                   │
                    ┌──────────────┴─────────────┐
                    │ Load rules for event/entity │
                    └──────────────┬─────────────┘
                                   │
                     ┌─────────────┴──────────────┐
                     │ For each rule:              │
                     │  Build SQL Expression       │
                     │  Inject parameters          │
                     │  Execute dynamically        │
                     └─────────────┬──────────────┘
                                   │
                          ┌────────┴────────┐
                          │ Condition passed?│
                          └───────┬──────────┘
                                  │YES
                                  ▼
                           Continue rule loop
                                  │
                                  │NO
                                  ▼
                     ┌───────────────────────────────┐
                     │ Log error + append message     │
                     │ If severity=Error → stop       │
                     └───────────────────────────────┘
                                  │
                                  ▼
                           ┌─────────────┐
                           │ RETURN      │
                           └─────────────┘

6. Dynamic SQL Generator (Core Engine)

Below is a simplified stored procedure that:

Loads rules
Loops through condition groups
Builds dynamic SQL
Evaluates TRUE/FALSE
Returns message

6.1 Stored Procedure Skeleton

CREATE PROCEDURE ExecuteRuleEngine
    @EntityName VARCHAR(200),
    @EventName VARCHAR(100),
    @JsonInput NVARCHAR(MAX)        -- entity payload from APIASBEGIN
    SET NOCOUNT ON;

    DECLARE @Errors TABLE (RuleId INT, Message VARCHAR(500));

    SELECT R.RuleId, R.RuleName, R.Severity, R.ErrorMessage
    INTO #Rules
    FROM RuleMaster R
    JOIN RuleEventMapping M ON R.RuleId = M.RuleId
    WHERE R.EntityName = @EntityName
      AND M.EventName = @EventName
      AND R.IsActive = 1;

    DECLARE @RuleId INT;

    DECLARE rule_cursor CURSOR FOR
        SELECT RuleId FROM #Rules;

    OPEN rule_cursor;
    FETCH NEXT FROM rule_cursor INTO @RuleId;

    WHILE @@FETCH_STATUS = 0
    BEGIN
        IF dbo.EvaluateRule(@RuleId, @JsonInput) = 0
        BEGIN
            INSERT INTO @Errors
            SELECT RuleId, ErrorMessage FROM #Rules WHERE RuleId = @RuleId;
        END

        FETCH NEXT FROM rule_cursor INTO @RuleId;
    END

    CLOSE rule_cursor;
    DEALLOCATE rule_cursor;

    SELECT * FROM @Errors;
END

7. Core Function: EvaluateRule()

This dynamically evaluates a rule using all its conditions.

CREATE FUNCTION EvaluateRule
(
    @RuleId INT,
    @JsonInput NVARCHAR(MAX)
)
RETURNS BIT
ASBEGIN
    DECLARE @SQL NVARCHAR(MAX) = '';
    DECLARE @Result BIT = 1;

    SELECT @SQL = STRING_AGG(
        '(' +
        CASE DataType
            WHEN 'Int' THEN 'CAST(JSON_VALUE(@JsonInput, ''$.' + LeftOperand + ''') AS INT)'
            WHEN 'Decimal' THEN 'CAST(JSON_VALUE(@JsonInput, ''$.' + LeftOperand + ''') AS DECIMAL(18,2))'
            WHEN 'Date' THEN 'CAST(JSON_VALUE(@JsonInput, ''$.' + LeftOperand + ''') AS DATETIME2)'
            ELSE 'JSON_VALUE(@JsonInput, ''$.' + LeftOperand + ''')'
        END 
        + ' ' + Operator + ' ' +
        CASE 
            WHEN LEFT(RightOperand,1)='@' THEN 'JSON_VALUE(@JsonInput, ''$.' + SUBSTRING(RightOperand,2,200) + ''')'
            ELSE QUOTENAME(RightOperand,'''')
        END + ')',
        ' AND '
    )
    FROM RuleCondition RC
    JOIN RuleConditionGroup G ON RC.GroupId = G.GroupId
    WHERE G.RuleId = @RuleId;

    DECLARE @FinalSQL NVARCHAR(MAX) = 'SELECT CASE WHEN ' + @SQL + ' THEN 1 ELSE 0 END';

    EXEC sp_executesql @FinalSQL, N'@JsonInput NVARCHAR(MAX)', @JsonInput=@JsonInput OUTPUT;

    RETURN @Result;
END

This function:

Converts JSON data into SQL values

Generates expressions like:

(CAST(JSON_VALUE(@json,'$.Quantity') AS INT) > CAST(JSON_VALUE(@json,'$.StockQty') AS INT))
AND
(JSON_VALUE(@json,'$.Status') <> 'Closed')

Evaluates the formula
Returns 1 or 0

8. Example: Real Rule Engine in Action

Rule

If OrderTotal > CreditLimit OR Customer is blocked → fail.

Conditions stored in database

Group	Left	Operator	Right	DataType
1	OrderTotal	>	CreditLimit	Decimal
1	IsBlocked	=	1	Int

Generated SQL

SELECT CASE WHEN
(
  CAST(JSON_VALUE(@json,'$.OrderTotal') AS DECIMAL(18,2)) >CAST(JSON_VALUE(@json,'$.CreditLimit') AS DECIMAL(18,2))
)
OR
(
  CAST(JSON_VALUE(@json,'$.IsBlocked') AS INT) = 1
)
THEN 0 ELSE 1 END

9. Logging & Monitoring

Use:

CREATE TABLE RuleEngineLog (
    RunId BIGINT IDENTITY PRIMARY KEY,
    RuleId INT,
    EntityName VARCHAR(200),
    InputPayload NVARCHAR(MAX),
    Result BIT,
    Message VARCHAR(500),
    CreatedDate DATETIME2 DEFAULT SYSDATETIME()
);

10. Best Practices

Do

Pre-validate JSON schema using ISJSON
Cache rule metadata
Avoid string concatenation inside cursor
Create reusable SQL UDFs (e.g., CompareValues)
Add unit tests for each rule

Avoid

Very large nested OR conditions
Using dynamic SQL inside loops without batching
Storing complex formulas directly in text fields

Final Notes

This SQL Rule Engine design is:

Flexible
Enterprise-grade
Deployment-free
Extensible
Highly maintainable
Perfect for ERP, WMS, Finance, Insurance, Enterprise SaaS