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