On this page

Documentation / Scripting: Arguments and parameters

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, a list (value, value, ...), or one existing variable written ${other}, which passes its value and type unchanged. 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.
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.
Values are always sent to the database as typed parameters, never pasted into the SQL text: an apostrophe needs no escaping and a value can never change the statement.

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:

the argument passed in the call (continent='Asia');
the session variable of the same name, set earlier with LET;
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. A list cannot be written in a LET; a Script keeps one with LET codes = ${codes};.

List parameters

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

-- countries/by-codes.sql
-- @param codes LIST OF TEXT "Three-letter country codes"
SELECT CODE, NAME FROM COUNTRY WHERE CODE IN (${codes}) ORDER BY CODE;

Call it with three codes:

@countries/by-codes.sql codes=('FRA', 'DEU', 'ITA');
SELECT CODE, NAME FROM COUNTRY WHERE CODE IN (${codes}) ORDER BY CODE
-- codes = ('FRA', 'DEU', 'ITA')
|----|----------------------------------------------------|
|CODE|NAME                                                |
|----|----------------------------------------------------|
|DEU |Germany                                             |
|FRA |France                                              |
|ITA |Italy                                               |
|----|----------------------------------------------------|

3 rows fetched in 0 ms.

Each element is sent to the database as its own typed parameter: IN (${codes}) becomes IN (?, ?, ?). Values never become SQL text.
A list has 1 to 1000 elements of type TEXT, INTEGER, DECIMAL, DATE or TIMESTAMP. Duplicates are kept.
${codes} must be alone between parentheses: IN (${codes}), NOT IN ( ${codes} ). Anywhere else, the statement is refused before it reaches the database.
A single value must still be written as a list: codes=('FRA').
A list variable can be copied (LET saved = ${codes};), passed to another Script (@other.sql ids=${codes}), printed by ECHO (('FRA', 'DEU', 'ITA')) and listed by SHOW SCRIPT VARIABLES with the type LIST OF TEXT.
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

Problem	Cause	Fix
`requires argument x (declared in @param)`	A required parameter was not passed and no variable `x` exists	Pass `x=value`, set it first with `LET x = ...`, or give it a default in its `@param` line
`(value of the session variable x; ...)`	A variable set earlier with `LET` has a value the parameter's type refuses	Pass `x=value` for this call, or set `x` again with a valid value
`argument x is declared DATE: '01/02/2026' is not a date`	Dates are written `'yyyy-MM-dd'` only	Write `'2026-02-01'`
`argument x is declared TEXT: 42 is a number`	A text value must be quoted (leading zeros matter)	Write `x='42'`
`a single value was given; write a list`	A `LIST OF` parameter needs a list	Write `x=(42)`
`argument ids is an empty list` or `contains NULL`	`IN ()` is not portable, and `IN (..., NULL)` never matches NULL	Pass at least one value, without `NULL`
`has 1001 elements; a list argument has at most 1000`	The list limit	Use a `<@file>` list source
`${ids} is a list: use it alone between parentheses`	A list used as a single value	Write `IN (${ids})`
`a list value needs a "-- @param ids LIST OF <type>" declaration`	A list passed to a Script that does not declare it	Declare the parameter with its element type
`@param line 3 cannot be read: unknown type NUMBER`	A mistake in the declaration: every call is refused	Fix the line; `LIB LINT` lists every such line
A `DATE` or `TIMESTAMP` value fails on Apache Derby	The Derby driver refuses date values sent as parameters	Declare the parameter `TEXT` and convert it in the query: `CAST(${d} AS DATE)`

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

Declared	Accepted	Sent as	Refused
`TEXT`	A quoted string	Text	A number, `TRUE`, `FALSE`
`INTEGER`	An integer	64-bit integer	A decimal number, a string, a boolean
`DECIMAL`	An integer or a decimal number	Exact decimal	A string, a boolean
`BOOLEAN`	`TRUE`, `FALSE`	Boolean	Anything else
`DATE`	`'yyyy-MM-dd'`, a real date	Date	Any other form, `'2026-02-30'`
`TIMESTAMP`	`'yyyy-MM-dd HH:mm:ss'`, optional fraction of a second, `T` instead of the space	Timestamp	An offset or a time zone

NULL is accepted for every type and sent as a NULL of the declared type.
A ${other} argument, and a session variable used for an omitted parameter, are converted from the variable's value by the same rules; a date variable is not accepted for a TIMESTAMP parameter.
A Script without @param keeps the plain literal types: 42 is an integer, '42' a string.

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} is written without quotes, unlike '%2': it is a typed value, not 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.