# Generic procedure dispatch

When the metadata names a stored procedure — via columns like
`gdsmodule.sSaveProName`, `sSaveProNameBefore`, `sDeleteProName`,
`sCalcProName`, `sProcName`, or `gdsconfigformslave.sButtonParam` —
the framework dispatches that proc by name. `sSaveProName` and
`sSaveProNameBefore` are **hooks**: they run as post-save / pre-save
phases on top of the always-running base add/update path
(`BusinessBaseServiceImpl.addBusinessData` /
`updateBusinessData`), invoked by `checkUpdate(...,"sSaveProName")`
at `BusinessBaseServiceImpl.java:1824` and `:1778`. The other
columns drive on-demand calls: `sCalcProName` for button-press
calculations, `sProcName` for custom-fetch flows, etc. The same
generic-dispatch machinery handles all of them.

The handler is `GenericProcedureCallController` in
`xlyEntry/com/xly/web/businessweb/`.

## Endpoint shape

The frontend POSTs to `/procedureCall/doGenericProcedureCall` with:

- The proc name (often resolved from `gdsmodule` or `gdsconfigformslave.sButtonParam`).
- The parameter values (frontend supplies them, the framework injects
  tenant identity).

The handler:

1. Loads the proc's signature (parameter names and types) from
   `information_schema.PARAMETERS`.
2. Composes `CALL <proc>(?, ?, …)` with parameters bound positionally.
3. Augments the call with `sBrandsId`, `sSubsidiaryId`, `sLoginId`,
   `sLanguage` from `RequestAddParamUtil`.
4. Executes via MyBatis.
5. Returns either the proc's result-set (for SELECTs inside the proc)
   or its OUT parameters (for save/calculate procs).

## Why dynamic proc dispatch matters

With well over a thousand stored procedures and hundreds of functions
in the schema, hard-wiring each one in Java would be untenable. Dispatch
by name lets the framework call any proc the metadata names without a
code change. The framework treats the proc as a black box: name in,
parameters in, result out.

That convenience comes with substantial costs that are worth being
explicit about:

- **No compile-time check** on proc names. A typo in
  `gdsmodule.sSaveProName` produces a runtime "proc not found"
  error, not a compile error. Refactoring a proc name requires
  hand-grepping the metadata; the IDE can't help.
- **No type safety on parameters.** The framework binds parameters
  positionally from a `Map<String, Object>`. A proc whose signature
  changed but whose callers didn't is a runtime crash with no IDE
  warning.
- **No call-site discoverability.** "Which Java code calls
  `Sp_SalSalesCheck`?" can't be answered by IDE find-usages because
  no Java code does — `gdsmodule` rows do. Maintainers must search
  *both* metadata tables *and* the SQL bodies of other procs that
  may invoke this one.
- **Effectively no static analysis.** Side effects of any given
  proc are invisible to anyone who hasn't read the proc body. A
  `Sp_SalSalesCheck` named in `gdsmodule.sProcName` could be a
  read-only SELECT or could be doing INSERTs and UPDATEs across a
  dozen tables; the framework treats them identically.
- **Stack traces that stop at the boundary.** Java errors thrown
  from inside a proc surface as a generic `BadSqlGrammarException`
  or `MySQLSyntaxErrorException`. To get the real error you have
  to enable MyBatis SQL logging and re-run.

A more honest framing: hard-wiring 1000+ procs in Java would be
painful, but most of that pain comes from xly *having* 1000+ procs
in the first place. Dynamic dispatch made it cheap to keep adding
them, which made the pile grow, which made the pile harder to
audit. The mechanism is what it is; the *amount* of behaviour
pushed into the SQL layer is the more interesting design question.

## The conventions procs follow

xly's procs share a calling convention to make generic dispatch
possible:

- **Common parameters in a fixed order:** typically
  `(IN sLoginId, IN sCustomerId, IN sBrId, IN sSuId, …)`. Cross-cutting
  identity always comes first.
- **Tenant-aware:** every proc accepts `sBrId` (= `sBrandsId`) and
  `sSuId` (= `sSubsidiaryId`) and uses them in every internal predicate
  to stay tenant-safe.
- **Output via OUT params:** save / calc procs return success / error
  through `OUT sCode INT, OUT sReturn LONGTEXT` — see
  `xlyEntry/src/main/resources/templates/templesql/sSaveProName.sql`
  for the canonical scaffold.
- **Dynamic SQL inside:** many procs build SQL strings with `CONCAT`
  + `PREPARE` + `EXECUTE`. This is xly's escape hatch for runtime
  shape variability and the reason `jSqlParser` is in the dependency
  tree (the framework occasionally inspects these strings).

## The companion file: `templesql/`

`xlyEntry/src/main/resources/templates/templesql/` holds **scaffolds**
for the standard proc kinds (eight files in the live tree):

- `sSaveProName.sql`
- `sDeleteProName.sql`
- `sProcName.sql`
- `sSaveProNameBefore.sql`
- `sButtonParam.sql`
- `sProTempleByDb.sql`
- `sp_btn_calc.sql`
- `sSqlStr.sql`

These are templates an engineer fills in to author a new proc — they
are not used by the runtime to generate procs on the fly. See [SQL
templates](sql-templates.md) for the loader and the placeholder syntax.

## Proc-name molds in the live schema

The 1687 procedures in the live DB cluster around a few naming molds
beyond the bare `Sp_*` family:

| Mold | Approx count | Role |
|---|---:|---|
| `Sp_*` | most | The dominant family, dispatched by `sSaveProName` / `sCalcProName` / `sProcName` etc. |
| `Sp_*_BeforeSave` | ~62 | Pre-save hooks. Pair with `sSaveProNameBefore`. |
| `Sp_*_AfterSave` / `Sp_*_SaveReturn` | ~62 / ~54 | Post-save hooks; `_SaveReturn` writes back into the parent transaction. |
| `Sp_*_Calc` | ~178 | Calculation procs invoked by button-press flow (`sCalcProName` / `sButtonParam`). |
| `sp_btn_*` | ~65 | Button-event sub-family — typically `sp_btn_calc*` / `sp_btn_validate*` (lowercase by convention). |
| `PRO_ERPMERGE*` | ~11 | Data-migration utilities. **Not dispatched by the runtime** — engineer-only. |
| `PRO_*` (other) | ~12 | Other one-off utilities. |
| `Get_*`, `del_*`, `Cal*`, `Tj_*` | small handfuls | Legacy / domain-specific helpers. Not part of the generic-dispatch contract. |

A typo in any of the dispatched columns gets an `Sp_*`-shaped target,
so other molds never resolve via `sSaveProName` / `sCalcProName` etc.
The non-`Sp_*` procs are reachable only via direct invocation in
mapper XML or other procs.

## The function layer

The schema also ships **177 user-defined functions** following parallel
naming molds: `Fun_*` (~150), `Fn_*` (~8), `get_*` (~10).

These are **not Java-dispatched**. They are invoked from inside other
procedures, view definitions, and mapper-XML SELECT statements. There
is no `gdsmodule.sFunctionName` column or analogous metadata —
functions are picked up by the SQL that mentions them. A maintainer
investigating a slow report should grep procs and views for `Fun_*` /
`Fn_*` / `get_*` references; the framework's Java side does not see
them at all.

## Failure modes to watch

1. **Mismatched parameter order.** Generic dispatch binds positionally;
   a proc whose `IN` parameters are reordered is a runtime explosion.
2. **Missing tenant filter.** A proc that forgets `sBrandsId` /
   `sSubsidiaryId` in any internal predicate is a multi-tenant leak.
   Procs are NOT auto-filtered by the framework — they have to do it
   themselves.
3. **Long-running procs blocking the request thread.** Long
   calculations should run on a separate worker (see `xlyErpTask`,
   though it's currently disabled in `settings.gradle`).