BroadSQL BroadSQL Downloads
On this page
Documentation / Scripting: Nested Scripts and run lifecycle

Nested Scripts and run lifecycle

Nested Scripts

A statement that starts with @ runs another Script, so Scripts can call Scripts:

LET region = 'EU';

@prepare-data.sql region=${region};
@create-report.sql region=${region};
  • A called Script uses the same variable namespace as its caller. It sees every variable, and the variables it assigns, including its arguments, are still there after it returns. Arguments are not local function parameters.
  • All the Scripts belong to the same top-level run: one Run ID, one status line at the end, and counts that include every statement of every called Script. A called Script prints no status line of its own.
  • A called Script that ends FAILED makes the calling statement fail, and the caller's ON ERROR policy applies. A called Script that ends COMPLETED_WITH_ERRORS does not, but its failures are counted, so the caller cannot end better than COMPLETED_WITH_ERRORS.
  • A Script that calls itself, directly or through other Scripts, is refused immediately with the chain of Scripts involved. Nesting is limited to 32 levels.

Run status and Run ID

When a Script started at the prompt, from the Editor or by DUMP LIB ends, one line gives its result:

load.bsql: COMPLETED_WITH_ERRORS (4 statements, 1 failed) [run OYS5JHED]
StatusMeaning
SUCCESSEvery statement succeeded, including the statements of the Scripts it called.
COMPLETED_WITH_ERRORSThe Script reached its end and at least one statement failed in it or in a Script it called.
FAILEDAn argument was invalid, the Script was refused before it started, or ON ERROR STOP ended it.
CANCELLEDYou pressed CTRL+C.

The counts include the statements of called Scripts. On a line typed at the prompt, a Script call whose status is not SUCCESS counts as a failed statement, so the rest of the line is not executed.

The Run ID in brackets (8 letters and digits) identifies one top-level Script execution. Every Script it calls shares it. It also appears in the application log, so log entries can be matched with the run you saw. It identifies a run, not a connection or a user. BroadSQL keeps no history of runs.

Cancelling a Script

CTRL+C while a Script is running cancels the whole top-level run: the running SQL statement is cancelled, no further statement runs at any nesting level, and the status is CANCELLED, whatever the ON ERROR policy. Variables keep every assignment completed before CTRL+C. When a SQL statement was cancelled, the usual rollback on a SQL error applies. At the prompt, CTRL+C cancels the running statement and skips the rest of the typed line.