How do I save a query with parameters and run it by name?

Applies to BroadSQL 5.5.0 · reviewed October 18, 2026

What you'll accomplish

Some queries come back every week with a different value: the same report for another continent, the same check for another list of codes. Keeping them in a text file and editing the value before each run is error prone, pasting a value into SQL by hand is how quotes go wrong, and a run with a forgotten value can quietly do the wrong thing.

You will save one query as a Script of the Scripts Library and make it safer step by step: a required text parameter, an optional number with a default, a list parameter for any number of values, and a value taken from the session. You will run each version by its name, and see what BroadSQL refuses, before any statement runs, when a value is missing or has the wrong type. Values are checked against their declared type and sent to the database as parameters, never as text pasted into the SQL.

Short answer

Save the query as a file under the scripts folder, write ${name} where a value goes, declare each parameter with -- @param <name> <TYPE> [= <default>], and run it with @<path> name=value. A parameter without a default is required; LIST OF <TYPE> takes a list, codes=('FRA', 'DEU').

1. Save the query with one required parameter

The Scripts Library is the scripts folder of your installation. Create a folder countries in it, and save this text as largest.sql, with any text editor or with EDIT countries/largest.sql;:

-- @description: Largest countries of a continent
-- @param continent TEXT "Continent, as in COUNTRY.CONTINENT"
SELECT CODE, NAME, POPULATION
  FROM COUNTRY
 WHERE CONTINENT = ${continent} AND POPULATION >= 50000000
 ORDER BY POPULATION DESC;

The first line describes the Script. The @param line declares continent, a text without a default: a required parameter. ${continent} marks where its value goes.

2. Run it by name

Connected to WORLD, run it by its path in the library, with the continent of your choice:

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

BroadSQL shows the value it used, the result, then the status of the run:

WORLD> @countries/largest.sql continent='Europe';
-- continent = 'Europe'
|----|----------------------------------------------------|-----------|
|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]

The value is sent to the database as a typed parameter: an apostrophe needs no escaping, and a value can never change the statement.

3. Forget the argument

Call the Script without continent:

@countries/largest.sql;

BroadSQL refuses the call and says how to give the value, with the message documented in Arguments and parameters:

WORLD> @countries/largest.sql;
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

Nothing ran and no variable was assigned: a report for no continent, or a cleanup without its date, cannot start.

4. Add an optional parameter with a default

Replace the fixed 50000000 with a second parameter. The = 50000000 gives it a default, which makes it optional:

-- @description: Largest countries of a continent
-- @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;

The call of step 2 still works, with the default. Pass both values to change it:

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

5. Pass a value of the wrong type

A value is converted to its declared type before the Script starts, so a wrong one refuses the call. This session is from Arguments and parameters:

@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]

0 statements: nothing reached the database. A text value is quoted ('Europe'); a number is not (100000000).

6. Take a value from the session

A parameter also takes the session variable of the same name, set with LET. Set it once, then run the Script without arguments:

LET continent = 'Africa';
@countries/largest.sql;

The run uses Africa. An argument of the call wins over the variable for that call only, and the call never changes the variable. When a value must always be given explicitly, even if a session variable exists, the older line -- @params: continent requires it in the call itself.

7. A list parameter for any number of values

A query for "these countries" needs as many values as the request names, and one parameter per value does not work when that number changes. Save a second Script, by-codes.sql, in the same folder; LIST OF TEXT declares a list of texts:

-- @description: Countries by ISO code
-- @param codes LIST OF TEXT "Three-letter country codes"
SELECT CODE, NAME FROM COUNTRY WHERE CODE IN (${codes}) ORDER BY CODE;

Run it with three codes, written as a list in parentheses:

@countries/by-codes.sql codes=('FRA', 'DEU', 'ITA');

BroadSQL shows the statement, the list it received, then one row per code. This session is from Arguments and parameters:

WORLD> @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 checked against the declared type and sent as its own parameter: IN (${codes}) becomes IN (?, ?, ?). The rules of a list:

• It holds 1 to 1000 values, without NULL. One value is still written as a list: codes=('FRA').
• ${codes} stands alone between the parentheses: IN (${codes}) or NOT IN (${codes}). Anywhere else, the statement is refused before it reaches the database.
• A list cannot be written in a LET. Inside a Script, @other.sql codes=${codes} passes the same list on.
• Numbers and dates work the same way: -- @param ids LIST OF INTEGER, called with ids=(2974, 3068).

8. See what a Script expects before running it

LIB SHOW prints the Script and its parameters, with their types and defaults:

LIB SHOW countries/largest.sql;

It lists continent, a required text, and min_population, an integer that defaults to 50000000.

9. The library command form, and keeping the result

LIB RUN runs a Script exactly like @, in the command family of the Scripts Library:

LIB RUN countries/by-codes.sql codes=('PRT');

To keep the result rather than read it, DUMP LIB runs the Script and writes its final result to a file:

DUMP LIB countries/largest.sql continent='Asia' TO asia_largest AS XLSX;

The file is written only when the run ends with SUCCESS (see Write a report Script that stops at the first failure).

Variations

• Find a Script later: LIB LIST;, LIB FIND largest; or LIB TREE; (see Organize my saved queries).
• Make a parameter optional without a value: = NULL as its default.
• Start from ready-made Scripts: the Scripts Gallery offers both Scripts of this page to download, including Countries by ISO code.

Watch out

• A plain name always starts at the root of the Scripts Library: nothing is searched in subfolders.
• continent is compared exactly: 'Europe', not 'europe'.
• A session value of the wrong type refuses the call too; the default is not used instead.
• A mistake in a -- @param line refuses every call: LIB LINT; lists such lines in the whole library.
• An older BroadSQL ignores @param lines.

Clean up

The Scripts are useful as they are. Delete asia_largest.xlsx from the export folder.

Related

• Arguments and parameters
• Running Scripts
• Script directives
• Run a post-deployment smoke check on every environment

Commands used

• @
• LIB RUN
• LIB SHOW
• LET
• DUMP

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