@parent Tokens

Syntax

When a Multi-Query template defines nested query nodes, each child SQL can reference columns from any ancestor row in the current execution stack using the @parent prefix. The engine resolves these tokens at runtime by inspecting the ancestor context stack built up as it walks the result tree from root to leaf.

How Resolution Works

Understanding the resolution lifecycle helps you write efficient templates and diagnose unexpected empty collections. The engine follows a depth-first traversal:

1. Root query executes. The top-level SQL in the template runs against the database with only the injected @TenantID and any caller-supplied query parameters. Every row in the result set is pushed onto the ancestor context stack, one row at a time, before the engine descends into that row's child queries.
2. Child SQL is parameterised per parent row. For each parent row, the engine walks the template's child query definitions. Every occurrence of @parent.X in the child SQL is substituted with the actual column value from the current parent row before the child query is sent to the database. The child executes as a normal parameterised query — no string interpolation occurs.
3. Recursion continues depth-first. The process is fully recursive. Each child row is itself pushed onto the ancestor context stack and becomes the parent for any grandchild queries defined under it. @parent.parent.X in a grandchild query resolves by walking two levels up the stack — to the original root row — and reading column X from that record.
4. Stack unwinds after leaf nodes. Once all children of a given row have been collected, the engine pops that row off the context stack and advances to the next sibling row at the same level. The final output is a complete nested structure reflecting the full record tree.

Examples

Direct Parent Reference

This is the most common pattern: a child query filtered by a column from its immediate parent row. Here, Payslips is a child of Employees, and each payslip query is scoped to one employee:

-- Child SQL for the "payslips" collection node
SELECT
    ps.PayslipID,
    ps.Period,
    ps.GrossPay,
    ps.NetPay,
    ps.ProcessedOn
FROM dbo.Payslips ps
WHERE ps.EmployeeID = @parent.EmployeeID
  AND ps.TenantID  = @TenantID
ORDER BY ps.Period DESC;

Grandparent Reference

When a third-level query (e.g. DeductionDetails under Deductions under Payslips) needs a column from the grandparent Payslips row — or the great-grandparent Employees row — chain additional .parent segments:

-- Child SQL for "deductionDetails" (three levels deep)
-- Needs DepartmentID from the Employees row (great-grandparent)
SELECT
    dd.DeductionDetailID,
    dd.LineDescription,
    dd.AppliedAmount,
    dd.RuleRef
FROM dbo.DeductionDetails dd
WHERE dd.DepartmentID = @parent.parent.DepartmentID
  AND dd.TenantID     = @TenantID
ORDER BY dd.DeductionDetailID;

Using the @parent.id Shortcut

For simple single-column primary keys, @parent.id saves you from repeating the column name. The engine resolves it to the value of the first identity column in the parent result set:

-- Equivalent to @parent.EmployeeID when EmployeeID is the first PK column
SELECT *
FROM dbo.EmployeeAddresses
WHERE EmployeeID = @parent.id
  AND TenantID   = @TenantID;

Performance Guidance

Additional performance recommendations:

• Index all columns referenced by @parent.X tokens (e.g. EmployeeID on dbo.Payslips).
• Include TenantID as the leading column in all covering indexes to enable tenant-scoped index seeks.
• Avoid SELECT * on wide tables — project only the columns consumed by the template or its children.
• Use TOP or FETCH NEXT on child queries when only a preview set is needed.

Null Safety

If a parent row contains a NULL value in the column referenced by an @parent.X token, the engine treats the child query as unevaluable for that row and returns an empty collection ([]) rather than executing a query with a NULL parameter. This prevents inadvertent cross-tenant data leaks and avoids full-table scans caused by WHERE col = NULL predicates.

The parent row itself is still included in the output — only its child collection is empty. You can detect this condition in consuming code by checking whether the child array is empty and whether the parent's key column is null.

Reserved Token Names

The following tokens are reserved by the engine and cannot be used as user-defined parameter names:

-- Correct for composite PK (CompanyID + EmployeeID)
WHERE t.CompanyID  = @parent.CompanyID
  AND t.EmployeeID = @parent.EmployeeID
  AND t.TenantID   = @TenantID