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:
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:
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:
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