# SQL templates (`xlyEntry/templesql/`)

The framework keeps a small library of **SQL scaffolds** under
`xlyEntry/src/main/resources/templates/templesql/`. They are not used
at runtime to generate SQL on the fly — they're templates an engineer
copies and fills in when authoring a new stored procedure that the
framework will invoke through [generic procedure dispatch](proc-dispatch.md).

## The scaffolds shipped

Eight files ship in the live tree (`xlyEntry/src/main/resources/templates/templesql/`):

| File | What it scaffolds |
|---|---|
| `sSaveProName.sql` | A standard save procedure with the conventional parameter list and OUT codes |
| `sSaveProNameBefore.sql` | A pre-save validation procedure |
| `sDeleteProName.sql` | A standard delete procedure |
| `sProcName.sql` | A generic logic procedure (audit/calc/check) |
| `sButtonParam.sql` | A procedure invoked by a button press, parameterised through `gdsconfigformslave.sButtonParam` |
| `sp_btn_calc.sql` | A button-driven calculation procedure |
| `sProTempleByDb.sql` | A procedure that switches behaviour by database / tenant |
| `sSqlStr.sql` | A SELECT-shape scaffold for `gdsconfigformmaster.sSqlStr` |

## Placeholder convention

Every scaffold uses `{placeholderName}` syntax (single braces, no
escaping). A real-world example from `sSaveProName.sql`:

```sql
DROP PROCEDURE IF EXISTS {sProName};
CREATE PROCEDURE {sProName}(
    IN sGuid VARCHAR(100),
    IN sFormGuid VARCHAR(100),
    IN sLoginId VARCHAR(100),
    IN sBrId VARCHAR(100),
    IN sSuId VARCHAR(100),
    OUT sCode INT(1),
    OUT sReturn LONGTEXT
)
BEGIN
    -- … placeholder logic …
    Update {sMasterTableName} AS A
        JOIN {sSlaveTableName} AS B
        ON A.sId = B.sProductId
    SET A.dProductQty = B.dProductQty
    WHERE B.sId = sGuid
      AND B.sBrandsId = sBrId
      AND B.sSubsidiaryId = sSuId;
END
```

The engineer copies this file, fills in `{sProName}`,
`{sMasterTableName}`, `{sSlaveTableName}`, replaces the placeholder
logic with the real business rules, and applies the resulting SQL to
the target schema.

## What the placeholders are for

- `{sProName}` — the procedure's name (e.g., `Sp_Sales_Save`).
- `{sMasterTableName}`, `{sSlaveTableName}`, `{sAllTableName}` — the
  document family the proc operates on.
- Other placeholders depending on the scaffold.

## Why this is a "template" library and not a code generator

The framework does **not** auto-generate procs from these templates
based on metadata. The scaffolds exist because xly's procs follow a
common conventional shape; copying the scaffold ensures the new proc:

- Accepts the standard parameter list `(sGuid, sFormGuid, sLoginId, sBrId, sSuId)`
  that [generic dispatch](proc-dispatch.md) can call.
- Returns success/error via the standard `OUT sCode INT, OUT sReturn LONGTEXT`.
- Honours the multi-tenant filter `sBrandsId = sBrId AND sSubsidiaryId = sSuId`.

A proc that *doesn't* follow these conventions cannot be invoked
through generic dispatch and would have to be called from custom Java
code instead.

## See also

- [Generic procedure dispatch](proc-dispatch.md) — how the framework
  invokes the procs once they're authored.
- [Slice 5 (per-customer SQL override)](../../slices/05-customer-sql-override.md)
  — when a customer needs a *replacement* of a standard proc, which
  reuses the same template structure but `DROPs` and `CREATEs` an
  override on the target schema.