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

Nested Scripts and run lifecycle

The run of a Script Five steps from left to right. The call, with name equals value arguments. The values: for each parameter, the argument of the call, else the session variable of that name, else its default. The checks: a missing or wrong value, or an unreadable Script, refuses the call and nothing runs. The statements run in order, nested Scripts included, under ON ERROR CONTINUE or STOP; Ctrl+C cancels the whole run. The end: a status, SUCCESS, COMPLETED_WITH_ERRORS, FAILED or CANCELLED, with a run ID, and the arguments are gone: each variable of the same name has its previous value again. 1. Call @s.sql year=2026 2. Values the argument, else a LET variable, else the @param default 3. Checks types, required values, readable Script Refused nothing runs 4. Run statements in order, nested Scripts, ON ERROR 5. Status SUCCESS, FAILED, COMPLETED_WITH_ ERRORS, CANCELLED Ctrl+C cancels the whole run. At the end, the arguments are gone: each variable of the same name has its previous value again. Each run gets a run ID, shown in its status line.

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 with LET are still there after it returns. Its arguments, and the defaults of its parameters, last only as long as the call: see Arguments and 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 and the database reported it as an error, the usual rollback on a SQL error applies; when CTRL+C arrives between two statements, or before a statement is sent, nothing is rolled back. At the prompt, CTRL+C cancels the running statement and skips the rest of the typed line. How fast a statement stops depends on the database: see Ctrl+C during a command.

A refused Production change stops the run

On a Connection of an Environment flagged Production, BroadSQL asks before each statement that can modify the database (see Production confirmation). Answering anything but yes stops the whole top-level run, nested Scripts included, whatever the ON ERROR policy, with the status CANCELLED and the message Run stopped: a Production change was refused at statement 3 of Script load.bsql (line 7). Statements executed before stay executed. Without an interactive terminal, such statements are refused, so a Script that modifies a Production database can only run from an interactive session.

Found something wrong or unclear on this page? , with the page address.