MemberJunction / MemberJunction/MJ
Support arithmetic expressions in query template parameters and composition pass-throughs
- Dominant language
- TSQL
- Stars
- 29
- Forks
- 6
- Avg merge
- 2d 1h
- Merged PRs (30d)
- 323
Description
## Summary
Two related gaps in the MJ SQL Query templating engine's parameter handling:
### 1. Arithmetic in template expressions
Expressions like `CAST({{ TargetYear }} - 1 AS NVARCHAR)` fail because the Nunjucks template outputs the raw parameter value and the surrounding SQL arithmetic doesn't get processed correctly when the parameter is used with a filter like `sqlString`.
**Current workaround**: Callers must pass a separate `PriorYear` parameter alongside `TargetYear`, which is redundant and error-prone.
**Desired behavior**:
```sql
-- This should work: render TargetYear as int, let SQL do the arithmetic
AND e.year = CAST({{ TargetYear }} - 1 AS NVARCHAR)
```
### 2. Arithmetic in composition pass-through parameters
The `{{query:"..."}}` composition syntax supports pass-through parameters like `(TargetYear=SomeParam)`, but only simple variable references. Expressions like `(TargetYear=TargetYear - 1)` are not supported.
**Current workaround**: Callers must define a separate parameter and pass it explicitly.
**Desired behavior**:
```sql
-- This should work: pass TargetYear - 1 to the dependency's TargetYear param
FROM {{query:"Golden-Queries/Membership/MSTA NAMS-DESE Member Bridge(TargetYear=TargetYear - 1)"}} bridge
```
## Use Case
A "Non-Returning Member District Switches" query needs to call a bridge query for `TargetYear - 1` (the prior year) while the outer query uses `TargetYear` (the current year). Without expression support, this requires a redundant `PriorYear` parameter that the caller must manually keep in sync with `TargetYear`.
## Impact
This affects any composition query where a dependency needs a parameter derived from the outer query's parameters rather than passed through verbatim.
Contributor guide
Assessment
This issue has not been assessed yet.