Instead of using hard-coded processes, you can build validation logic as data using a SQL based Rule Engine.  In ERP, CRM, WMS, finance, banking, healthcare, and dynamic form engines where business rules are constantly changing yet deployments are costly or time-consuming this is quite helpful.  This article describes how to create a dynamic rule engine that is fully stored in SQL using:

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

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.

Rule Engine Table Design
Below is a battle-tested schema used in real enterprise systems.

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”

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

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:

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"
);

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

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

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

Example: Real Rule Engine in Action
Rule
If OrderTotal > CreditLimit OR Customer is blocked → fail.

Conditions stored in database

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

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

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

HostForLIFEASP.NET SQL Server 2022 Hosting