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.

The scaffolds shipped

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

Placeholder convention

Every scaffold uses {placeholderName} syntax (single braces, no escaping). A real-world example from sSaveProName.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 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 — how the framework invokes the procs once they're authored.
• Slice 5 (per-customer SQL override) — 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.