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, then written into the query where it says ${name}.

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: BroadSQL writes the value there, as text, when the Script runs, so a text value goes between quotes, '${continent}', as any SQL string.

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, then ${codes} is written as the inside of a SQL value list: IN (${codes}) becomes IN ('FRA', 'DEU', 'ITA'). A text or a date element is written as a SQL string, its apostrophes doubled; a number as it is. The rules of a list:

• It holds 1 to 1000 values, without NULL. One value is still written as a list: codes=('FRA').
• A list of numbers is written the same way: with -- @param ids LIST OF INTEGER, WHERE ID IN (${ids}) becomes WHERE ID IN (2974, 3068).
• A list is not a LET value: LIST assigns and copies lists, LIST kept = (${codes});. 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.