Skip to content
PYD ENTERPRISES
Start free trial

T-SQL anti-pattern

T-SQL dynamic SQL rules

Stored procedures that assemble SQL strings from rule tables and parameters and run them with EXEC or sp_executesql, so the business logic is partly data and invisible to plain code reading.

Request a codebase scanExplore demo
FAMILY
T-SQL
CUTOVER RISK
High
Hides business logic or coupling that a rewrite will drop unless it is extracted explicitly.
MODERN MAPPING
Parameterised query with criteria as a reviewed decision table
GENERATED ARTIFACT
ELIGIBILITY.dmn.json

Before and after

legacy source → modern mapping

Legacy: exhibits the pattern

usp_SelectEligibleMembers.sqlSQL
1CREATE PROCEDURE dbo.usp_SelectEligibleMembers
2  @PlanCode VARCHAR(10), @AsOf DATE
3AS
4BEGIN
5  DECLARE @sql NVARCHAR(MAX), @rule NVARCHAR(4000);
6 
7  SELECT @rule = WhereClause
8    FROM dbo.EligibilityRules
9   WHERE PlanCode = @PlanCode AND Active = 1;
10 
11  SET @sql = N'SELECT m.MemberId FROM dbo.Members m '
12    + N'JOIN dbo.Coverage c ON c.MemberId = m.MemberId '
13    + N'WHERE c.PlanCode = @p '
14    + N'AND @d BETWEEN c.StartDate AND c.EndDate '
15    + N'AND (' + @rule + N')';      -- NULL rule: NULL @sql
16 
17  EXEC sp_executesql @sql,
18    N'@p VARCHAR(10), @d DATE', @p = @PlanCode, @d = @AsOf;
19END

Modern: Parameterised query with criteria as a reviewed decision table

EligibilityQuery.csC#
1public async Task<IReadOnlyList<string>> EligibleAsync(
2    string plan, DateOnly asOf)
3{
4    var criteria = _rules.For(plan)       // reviewed DMN
5        ?? throw new NoActiveRuleException(plan);
6 
7    return await _db.Members
8        .Where(m => m.Coverages.Any(c =>
9            c.PlanCode == plan &&
10            c.StartDate <= asOf && asOf <= c.EndDate))
11        .Where(criteria.ToPredicate(asOf))
12        .Select(m => m.MemberId)
13        .ToListAsync();
14}

Why it is dangerous in modernization

SQL Server applications often keep eligibility, pricing or routing criteria as WHERE-clause fragments in a rules table, and a stored procedure concatenates them into a query at runtime. The code shows the plumbing (DECLARE @sql, string concatenation, EXEC sp_executesql) while the rules live in rows that analysts edit through an admin screen. Static analysis of the procedure alone sees no rule at all. The pattern also carries real defects: concatenating a NULL fragment makes the whole string NULL under the default CONCAT_NULL_YIELDS_NULL setting, and sp_executesql with a NULL statement runs nothing and returns no result set, so a plan with no active rule silently has no eligible members. Fragments concatenated from user input are an injection risk. Migrations that port the procedure and forget the rule rows lose the business logic entirely.

How PYD ENTERPRISES detects it

The T-SQL front end parses procedures, functions and their batches, and models string-typed variables with an abstract string domain: concatenations, REPLACE, QUOTENAME and CASE produce sets of possible SQL shapes. Where a fragment comes from a table, PYD ENTERPRISES reads the rule table from a data extract you supply (only that table, inside your environment) and substitutes each row's fragment. Every resulting statement is parsed as SQL, so each rule row becomes an extracted rule linked to both the row and the procedure line that executes it. Fragments that can reach EXEC from parameters without QUOTENAME or parameterisation are flagged as injectable.

Maps to: Parameterised query with criteria as a reviewed decision table

The fixed part of the query becomes a parameterised query or a repository method. The criteria rows become a DMN decision table, one rule per row, reviewed and versioned like code, and evaluated in the service or compiled into query predicates. The NULL-rule case becomes an explicit decision: no active rule means no members, or an error, but never an accident.

Generated specification fragment

ELIGIBILITY.dmn.jsonJSON
1{
2  "decision": "Member eligible for plan",
3  "hitPolicy": "ANY",
4  "inputs": ["planCode", "relationship", "ageAsOf"],
5  "rules": [
6    { "id": "R1", "planCode": "\"PPO-FAM\"",
7      "relationship": "\"SUBSCRIBER\",\"SPOUSE\"",
8      "ageAsOf": "-", "eligible": true,
9      "source": "EligibilityRules row 14" },
10    { "id": "R2", "planCode": "\"PPO-FAM\"",
11      "relationship": "\"CHILD\"",
12      "ageAsOf": "< 26", "eligible": true,
13      "source": "EligibilityRules row 15" }
14  ],
15  "executedBy": "usp_SelectEligibleMembers.sql:17-18"
16}

Every generated element links back to the source file and line range shown in the legacy pane. Review and sign off before building against it.

The rules are in the rows

The procedure above contains no eligibility rule. The rules look like this, edited over the years by business analysts through an internal admin page:

dbo.EligibilityRules (extract)SQL
1-- RuleId PlanCode  Active WhereClause
2-- 14     PPO-FAM   1      m.Relationship IN ('SUBSCRIBER','SPOUSE')
3-- 15     PPO-FAM   1      m.Relationship = 'CHILD'
4--                         AND DATEDIFF(year, m.BirthDate, @d) < 26

Two rows, one assignment

Rows 14 and 15 are both active for PPO-FAM, but SELECT @rule = WhereClause assigns from each row in turn and keeps only the last value, and without ORDER BY SQL Server does not define which row that is. So the procedure applies either the subscriber rule or the child rule, never both. The intended logic was almost certainly an OR of the two. PYD ENTERPRISES reports multi-row variable assignment on a query that can return several rows as a finding, and the DMN table uses an ANY hit policy so the reviewed intent is explicit.

DATEDIFF(year, ...) also counts calendar year boundaries crossed, not completed years, so a child born in December is treated as a year older from January. That is preserved in the generated fixtures and called out for review.

Security review comes free

Because every EXEC site is analysed for the possible shapes of its string, the same pass finds injection paths: parameters concatenated into SQL without QUOTENAME or parameter binding. In this example, the fragment comes from a table, not a parameter, so it is not directly injectable, but anyone with write access to EligibilityRules can run arbitrary SQL in the procedure's security context. The finding lists that table as a privileged object for the security team.

Related patterns

Usually found in the same codebase.

Back to the full catalog

Find every T-SQL dynamic SQL rules in your codebase.

Run PYD ENTERPRISES in your private VPC and get each occurrence with file, line range, modern mapping and equivalence fixtures, ready for your architects to review.

Start 14-day trialBook technical walkthrough