BroadSQL BroadSQL Downloads
On this page
Documentation / Scripting: Arguments and parameters

Arguments and parameters

Named Script arguments

A Script receives named arguments. Each one is written name=value after the Script reference:

@customer-report.sql country='FR' min_id=100
LIB RUN customer-report.sql country='FR' min_id=100

The Script reads them as variables:

-- customer-report.sql
-- @params: country, min_id
SELECT ID, NAME FROM CUSTOMER WHERE COUNTRY = ${country} AND ID >= ${min_id};
  • A value is a number, a single-quoted string, TRUE, FALSE, NULL, or one existing variable written ${other}, which passes its value and type unchanged: @customer-report.sql country=${country} min_id=1. A query or an expression is not a value: compute it with LET first.
  • Arguments are separated by spaces, in any order; spaces around = are optional. There is no limit on their number: the former limit of nine parameters is gone.
  • Every value is evaluated before any argument is assigned. In @x.bsql a=${b} b=1, a receives the value b had before the call.
  • If any argument is invalid, or the Script cannot start, none of the arguments is assigned.
  • An argument is an assignment, exactly like a LET run just before the Script's first statement. It lands in the shared variable namespace and is still there after the Script returns, with the value it last received. There is no local scope and no previous value is restored. If the caller needs to keep a value, it copies it first: LET saved_id = ${customer_id};.
  • An argument that the Script does not use is accepted and simply becomes a variable.

Required arguments: -- @params:

A Script lists the arguments that every call must pass with a metadata line:

-- @params: country, min_id

A call that does not pass one of them is refused before anything runs, **even when a variable of that name already exists in the session**:

ERROR: report.bsql requires argument country (declared in @params): pass it explicitly as name=value, even when a session variable of that name exists
report.bsql: FAILED (0 statements, 0 failed) [run R6I425UY]

To pass an existing variable on, write it explicitly: @customer-report.sql country=${country} min_id=${min_id}.

  • @params has no types, no default values and no optional arguments, and it does not create local variables: once the call succeeds, the values are ordinary shared variables.
  • The line can be repeated; the names accumulate. LIB SHOW and the Editor display them, and Run in the Editor asks for one value per declared name.
  • Without @params, nothing is checked at the call: a variable that nobody defined fails the statement that uses it.

From %1..%9 to named arguments

The positional parameters %1 to %9 were removed. They are not supported anymore, in any form.

Before:

@report.sql 42 FR

-- report.sql
SELECT * FROM CUSTOMER WHERE ID = %1 AND COUNTRY = '%2';

Now:

@report.sql id=42 country='FR'

-- report.sql
-- @params: id, country
SELECT * FROM CUSTOMER WHERE ID = ${id} AND COUNTRY = ${country};
  • A call with values but no names fails with an explanation: Invalid script argument '42': positional script arguments (%1..%9) were removed: pass arguments as name=value and read them with ${name}. DUMP LIB and DUMP @ refuse them in the same way.
  • In a Script, %1 is ordinary text and is no longer replaced.
  • ${country} is written without quotes, unlike '%2': it is a typed value, not text.
  • LIB LINT reports every %1 to %9 left in the library.