Arguments and parameters

Pass values to a Script as name=value arguments, and declare in the Script which parameters it takes, of which type, and which ones are optional.

Named Script arguments

Step 1. Write the Script with ${name} where a value goes, and declare its parameters:

-- countries/largest.sql
-- @param continent TEXT "Continent, as in COUNTRY.CONTINENT"
-- @param min_population INTEGER = 50000000 "Smallest population listed"
SELECT CODE, NAME, POPULATION
  FROM COUNTRY
 WHERE CONTINENT = '${continent}' AND POPULATION >= ${min_population}
 ORDER BY POPULATION DESC;

Step 2. Call it with name=value arguments, with @ or LIB RUN:

@countries/largest.sql continent='Europe';

Step 3. BroadSQL checks and converts every argument, then runs the Script. In a Script, each value used is shown under the statement:

-- continent = 'Europe'
-- min_population = 50000000
|----|----------------------------------------------------|-----------|
|CODE|NAME                                                |POPULATION |
|----|----------------------------------------------------|-----------|
|RUS |Russian Federation                                  |146934000  |
|DEU |Germany                                             |82164700   |
|GBR |United Kingdom                                      |59623400   |
|FRA |France                                              |59225700   |
|ITA |Italy                                               |57680000   |
|UKR |Ukraine                                             |50456000   |
|----|----------------------------------------------------|-----------|

6 rows fetched in 12 ms.

countries/largest.sql: SUCCESS (1 statement, 0 failed) [run BUZ2SI4X]

How arguments work:

• A value is a number, a single-quoted string, TRUE, FALSE, NULL or a list (value, value, ...). A query or an expression is not a value: compute it with LET first.
• A ${other} variable in the call is replaced by its text before the call is read, as in every statement: write country='${c}' to pass a string, limit=${n} for a number, ids=(${list}) for a list.
• Arguments are separated by spaces, in any order; spaces around = are optional. There is no limit on their number.
• Every value is evaluated and checked before any argument is assigned. If one argument is refused, or the Script cannot start, no statement runs and no variable is assigned.
• An argument is a variable for the duration of the call: the Script reads it as ${name}, and so do the Scripts it calls. When the Script returns, a variable of that name gets back the value it had before the call, or is undefined again. A LET run by the Script itself stays: to keep a value after the Script, assign it there, for example LET last_country = '${country}';.
• An argument the Script does not declare is accepted, and is a variable for the call like any other argument.
• In the Script, ${name} is replaced by the argument's value, as text, in each statement that uses it: write the quotes a string needs (WHERE CONTINENT = '${continent}'). Nothing is escaped. See Variables.

Typed parameters: -- @param

Each parameter is declared on its own metadata line:

-- @param <name> <type> [= <default>] ["<description>"]

• <type> is TEXT, INTEGER, DECIMAL, BOOLEAN, DATE, TIMESTAMP, or LIST OF <type> (see List parameters). Length and precision are left to the database.
• The description is between double quotes; write "" for one double quote.
• The colon form -- @param: city_id INTEGER works too, as for every metadata line.

BroadSQL converts each argument to its declared type before the Script starts, the same way from the command line and from the BroadSQL Editor's Run dialog (where values are typed as in a form, without SQL quotes for a TEXT). A wrong value refuses the call:

@countries/largest.sql continent='Europe' min_population='many';
ERROR: countries/largest.sql: argument min_population is declared INTEGER: 'many' is a text value
countries/largest.sql: FAILED (0 statements, 0 failed) [run UI052EGR]

The conversion rules are listed in Reference: conversions.

LIB SHOW prints the parameters as a table:

LIB SHOW countries/largest.sql;
Content of Scripts Library script: countries/largest.sql
Parameters:
|--------------|-------|--------|--------|----------------------------------|
|Name          |Type   |Required|Default |Description                       |
|--------------|-------|--------|--------|----------------------------------|
|continent     |TEXT   |Yes     |        |Continent, as in COUNTRY.CONTINENT|
|min_population|INTEGER|No      |50000000|Smallest population listed        |
|--------------|-------|--------|--------|----------------------------------|

Optional parameters and defaults

A parameter with a default (= <value>) is optional; one without is required. = NULL is a valid default.

Where a parameter's value comes from

For each -- @param parameter, the Script uses the first of:

1. the argument passed in the call (continent='Asia');
2. the session variable of the same name, set earlier with LET;
3. the default declared in the @param line.

A required parameter with none of the three refuses the call. Set the values once, then run the Script without arguments:

LET continent = 'Europe';
LET min_population = 1;
@countries/largest.sql;

The Script runs with continent = 'Europe' and min_population = 1, and shows them under its statement:

-- continent = 'Europe'
-- min_population = 1

An argument overrides the session variable for that call only:

@countries/largest.sql continent='Asia';

This call uses Asia and 1. Afterwards, continent is still 'Europe'.

• The call never changes the session variables: the arguments, the session values and the defaults it uses hold for the call only. A variable that did not exist before the call does not exist after it.
• A session value is converted and checked exactly like an argument. A value of the wrong type refuses the call; the default is not used instead:

LET min_population = 'many';
@countries/largest.sql continent='Europe';
ERROR: countries/largest.sql: argument min_population is declared INTEGER: 'many' is a text value (value of the session variable min_population; pass min_population=<value> to override it)

• A missing required argument refuses the call:

@countries/largest.sql min_population=1;
ERROR: countries/largest.sql requires argument continent (declared in @param): pass it as name=value, or define a variable of that name with LET before the call

• A refused call runs no statement and changes no variable.
• A list parameter is completed the same way by a list variable: one assigned with LIST, or the list parameter of a calling Script. LET does not hold lists.

List parameters

A LIST OF <type> parameter takes several values, used in an IN list:

-- cities/by-ids.sql
-- @param ids LIST OF INTEGER "City identifiers"
SELECT ID, NAME, COUNTRYCODE FROM CITY WHERE ID IN (${ids}) ORDER BY ID;

Call it with three identifiers:

@cities/by-ids.sql ids=(2974, 3068, 3580);

The statement runs as SELECT ID, NAME, COUNTRYCODE FROM CITY WHERE ID IN (2974, 3068, 3580) ORDER BY ID, and the values used are listed under it: -- ids = (2974, 3068, 3580).

• ${ids} is written as a SQL value list with its parentheses: IN ${ids} becomes IN (2974, 3068, 3580). Where the reference is already between parentheses, as in IN (${ids}), no second pair is added. A number is written as it is; a text, a date or a timestamp as a SQL string, its apostrophes doubled: with codes=('FRA', 'O''X'), IN ${codes} becomes IN ('FRA', 'O''X').
• A list is kept after the call with LIST: LIST kept_codes = ${codes};. A LIST variable is passed to a Script the same way: @report.sql codes=${kept_codes}.
• A list has 1 to 1000 elements of type TEXT, INTEGER, DECIMAL, DATE or TIMESTAMP. Duplicates are kept.
• A single value must still be written as a list: ids=(2974).
• A list is passed to another Script as a list literal: @other.sql ids=(${ids}). It is listed by SHOW SCRIPT VARIABLES with its type, for example LIST OF INTEGER.
• For a list longer than 1000 values, read it from a file with a <@file> list source.

Required arguments: -- @params:

The older -- @params: line lists the names every call must pass, without types:

-- @params: country, min_id

A call that does not pass one of them is refused before anything runs. Unlike a -- @param parameter, a @params name is never taken from a session variable:

ERROR: report.bsql requires argument country (declared in @params): pass it explicitly as name=value, even when a session variable of that name exists

@params keeps working unchanged. For a new Script, prefer -- @param, which adds types, defaults and descriptions. When a Script declares a name in both, the name is required and its @param type applies; giving it a default in @param is a contradiction that refuses every call.

Limits and troubleshooting

An older BroadSQL ignores @param lines: a Script relying on a default fails there with Variable x is not defined at its first use.

Reference: conversions

• NULL is accepted for every type and written NULL; SHOW SCRIPT VARIABLES still shows the declared type.
• A session variable used for an omitted parameter is converted from the variable's value by the same rules; a date variable is not accepted for a TIMESTAMP parameter. A ${other} written in the call is its text, then read as the literal it forms: day='${d}' is the string '2026-10-19', converted by a DATE declaration.
• A Script without @param keeps the plain literal types: 42 is an integer, '42' a string.

Related pages

• Scripting overview
• Variables: the values and the shared namespace that arguments are part of.
• Running Scripts: Script references and the checks made before a Script starts.
• Scripts Library: the metadata header that @param belongs to, and LIB LINT.
• BroadSQL Editor: the Run dialog.
• Command reference: @, LIB RUN, LIB SHOW, LIB LINT.

From %1..%9 to named arguments

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

Before:

@report.sql 2974 FRA

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

Now:

@report.sql id=2974 country='FRA'

-- report.sql
-- @param id INTEGER
-- @param country TEXT
SELECT * FROM CITY WHERE ID = ${id} AND COUNTRYCODE = '${country}';

• A call with values but no names fails with an explanation: Invalid script argument '2974': 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}' keeps its quotes, like '%2': the value is written into the statement as text.
• LIB LINT reports every %1 to %9 left in the library.

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