← Back to Guide

Building Dynamic SQL

Most real queries aren't static — you add a filter here, an optional clause there, an IN list built from a collection. That's the moment developers reach for a StringBuilder and start appending fragments by hand. You don't have to. With one small compose step, raw string literals handle dynamic SQL cleanly: the static skeleton stays readable and a whole class of assembly bugs disappears. This page builds on Raw String Literals.

Where append-and-branch goes wrong

Here's a filter most of us have written some version of — a StringBuilder that appends a WHERE, then conditions, branching on which filters are set:

StringBuilder s = new StringBuilder();
s.Append("SELECT matrix.* ");
s.Append(" FROM OpsO.ProcLob.matrix ");
if (colorfilter != "All")
{
    s.Append(" WHERE ");

    if (colorfilter == "All Except Green")
    {
        s.Append(" matrix.Color != 'Green'  ");
    }
    else
    {
        s.Append(" matrix.Color = @colorfilter  ");
    }
    if (jurisfilter != "All")
    {
        s.Append(" AND matrix.Jurisdiction = @jurisfilter ");
    }
}
else if (jurisfilter != "All")
{
    s.Append(" WHERE ");
    s.Append(" matrix.Jurisdiction = @jurisfilter ");
}

It works, but it's fragile in three ways:

• Manual spacing. Every fragment has to carry its own leading and trailing spaces. Miss one and the concatenation silently fuses tokens into a runtime SQL error.
• The WHERE keyword is written by hand in more than one branch. Today the else if keeps them mutually exclusive — but the moment someone changes that else if to a plain if, you get WHERE ... WHERE ... and a broken query, with no compiler warning.
• The conditions are buried in string plumbing, which hides real SQL bugs. There's one hiding in the code above — see The NULL trap below.

Compose conditions into a list

The fix is to stop appending and start collecting. Gather each condition into a List<string>, join them with AND, and prepend WHERE only when the list is non-empty. The static skeleton — SELECT and FROM — stays a clean raw string literal; you interpolate just the assembled clause:

var conditions = new List<string>();
var parameters = new List<(string Name, object Value)>();

if (colorfilter == "All Except Green")
{
    conditions.Add("(matrix.Color != 'Green' OR matrix.Color IS NULL)");
}
else if (colorfilter != "All")
{
    conditions.Add("matrix.Color = @colorfilter");
    parameters.Add(("@colorfilter", colorfilter));
}

if (jurisfilter != "All")
{
    conditions.Add("matrix.Jurisdiction = @jurisfilter");
    parameters.Add(("@jurisfilter", jurisfilter));
}

string whereClause = conditions.Count == 0
    ? ""
    : "WHERE " + string.Join(" AND ", conditions);

string sql = $"""
    SELECT matrix.*
    FROM   OpsO.ProcLob.matrix
    {whereClause}
    """;

var q = new DbQuery(sql) { Source = conn };
foreach (var (name, value) in parameters)
    q.AddParameter(name, value);

What this buys you:

• WHERE appears exactly once, or not at all — driven by whether the list is empty. The double-WHERE class of bug can't happen, no matter how the branches evolve.
• No manual spacing. string.Join owns the separators, so there are no fused tokens.
• Each condition and the parameter it references are added together, so the SQL text and its parameters can't drift apart as the method grows.

Interpolate fragments, parameterize values

One rule keeps this safe: interpolate SQL fragments you authored — the assembled whereClause — and never interpolate a value. The color and jurisdiction always ride in as @parameters through AddParameter. Because the only thing entering the SQL text is developer-written condition strings, there's no injection surface, whatever the filter values happen to contain.

The rarer case — interpolating an identifier such as a database or schema name, which can't be passed as a parameter — carries its own safety rules (validation, collation) and gets a dedicated page in this section. See Raw String Literals for the interpolation basics.

The NULL trap

Clean composition also surfaces the kind of bug that plumbing hides. Look again at the "All Except Green" condition from the original:

-- Drops NULL-colored rows (the bug):
matrix.Color != 'Green'

-- Keeps them (the intent):
(matrix.Color != 'Green' OR matrix.Color IS NULL)

In SQL Server, NULL != 'Green' evaluates to UNKNOWN, not TRUE, and WHERE keeps only rows that test TRUE — so the naive form silently drops every row whose Color is NULL. If "All Except Green" is meant to include the uncolored rows, spell the NULL intent out. This is three-valued logic, and it bites any comparison against a nullable column.

IN lists and loops

When a condition comes from a collection — an IN list — the same rule holds: never concatenate the values into the SQL. Generate a parameter name per element in the loop, join the names into the query, and bind each value separately:

var colors = new[] { "Green", "Amber", "Red" };
var names = new List<string>();
for (int i = 0; i < colors.Length; i++)
    names.Add($"@color{i}");

string sql = $"""
    SELECT matrix.*
    FROM   OpsO.ProcLob.matrix
    WHERE  matrix.Color IN ({string.Join(", ", names)})
    """;

var q = new DbQuery(sql) { Source = conn };
for (int i = 0; i < colors.Length; i++)
    q.AddParameter(names[i], colors[i]);

The SQL text contains only @color0, @color1, and so on — the actual values never touch the string.

Rules of thumb

• Keep the static skeleton as a raw string literal; compute the dynamic parts separately.
• Collect conditions in a List<string> and string.Join(" AND ", ...) them.
• Prepend WHERE only when there's at least one condition.
• Interpolate fragments you wrote; parameterize every value.
• Add each parameter right next to the condition that uses it.

← Back to Guide