# 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.

The downside: the runtime cannot statically know which procs exist or
what their effects are. A typo in `gdsmodule.sSaveProName` produces a
runtime "proc not found" error, not a compile error.

## 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`).