Variables

A variable holds one value. Assign it with LET, then write ${name} anywhere in a later statement: BroadSQL replaces it with the value, as text, just before the statement runs.

LET country = 'FRA';
LET max_id = SELECT MAX(ID) FROM CITY;

SELECT ID, NAME FROM CITY WHERE COUNTRYCODE = '${country}' AND ID <= ${max_id};
DUMP (SELECT * FROM CITY WHERE COUNTRYCODE = '${country}') TO cities_${country} AS XLSX;

The SELECT runs as SELECT ID, NAME FROM CITY WHERE COUNTRYCODE = 'FRA' AND ID <= followed by the highest city ID, and the DUMP writes cities_FRA.xlsx. The examples of this page run on the WORLD sample (CONNECT WORLD;).

Three ways to assign a value

• Literal assignment: a number (42, -1.5), a single-quoted string ('O''Brien'), TRUE, FALSE or NULL. There are no expressions: LET n = 1 + 1 is refused.
• Scalar query assignment: a query that starts with SELECT, WITH or VALUES and returns exactly one row and one column. It runs on the current connection, in the current transaction, so it sees your uncommitted changes. No row, several rows or several columns are errors: the first row is never taken silently. A NULL value is assigned as NULL, so SELECT MAX(ID) on an empty table gives NULL, not an error. Large objects, binary and structural columns cannot be held by a variable. The query's result is never displayed and never becomes the "last result" used by /, DUMP / or <@last:...>.
• From another variable: since ${name} is replaced first, write the value as a literal: LET saved_id = ${max_id}; for a number, LET saved_name = '${name}'; for a string, LET saved_day = SELECT DATE '${day}'; for a date.

LET holds single values. A list is assigned with LIST: values separated by commas, or a query that returns one column. ${name} writes a list with its parentheses, so a list is used and copied as it is:

LIST codes = 'FRA', 'DEU', 'ITA';
LIST ids = 2974, 3068;
LIST european = SELECT CODE FROM COUNTRY WHERE CONTINENT = 'Europe';
SELECT CODE, NAME FROM COUNTRY WHERE CODE IN ${codes};
LIST saved_codes = ${codes};

The SELECT runs as ... WHERE CODE IN ('FRA', 'DEU', 'ITA'), and LIST saved_codes = ${codes}; as LIST saved_codes = ('FRA', 'DEU', 'ITA');: a copy, which keeps its values when codes changes. In a Script, LIST keeps a LIST OF parameter after the call, since a parameter lasts only as long as the call.

A list has 1 to 1000 values, all texts, all numbers or all booleans; NULL values are kept. A list read from a query keeps the type of the column: a number stays a number, and a text that looks like a number ('123') stays a text. SHOW SCRIPT VARIABLES shows a list as LIST OF TEXT, LIST OF NUMBER or LIST OF BOOLEAN. A name holds either a single value or a list: LIST refuses a name assigned with LET, and LET a name assigned with LIST.

Names are made of letters, digits and _, start with a letter or _, have at most 64 characters, and are case-insensitive. ENV, NULL, TRUE and FALSE are reserved. A failed assignment leaves the variable unchanged. Each successful LET prints a confirmation, such as country = 'FRA' (VARCHAR).

${name} is replaced as text, everywhere

LET TOTO = 'blabla';

SELECT * FROM T WHERE a = '${TOTO}';
UPDATE T SET a = '${TOTO}';
DUMP T TO MyReport_${TOTO} AS XLSX;
CONNECT ${TOTO};

These statements run as:

SELECT * FROM T WHERE a = 'blabla';
UPDATE T SET a = 'blabla';
DUMP T TO MyReport_blabla AS XLSX;
CONNECT blabla;

What this means in practice:

• Every statement: SQL, every BroadSQL command (CONNECT, ENV, DUMP, LOAD, REPEAT, ECHO...), the arguments of a Script call, file names and paths, the statements of a Script, of a Script it calls, of a login script and of the BroadSQL Editor's Run. There is no list of commands that accept variables.
• Inside quotes too: '${TOTO}' becomes 'blabla'. A text value therefore goes between quotes, as any SQL string: WHERE COUNTRYCODE = '${country}'. Without quotes, WHERE COUNTRYCODE = ${country} becomes WHERE COUNTRYCODE = FRA, which names a column.
• Nothing is escaped: a value that contains an apostrophe ends the SQL string. With LET name = 'O''Brien';, WHERE NAME = '${name}' becomes WHERE NAME = 'O'Brien', which is invalid SQL. To use such a value, store it with its apostrophe doubled: LET name = 'O''''Brien';.
• Any piece of a statement: a value can be a table name, a column or a condition: LET t = 'CITY'; SELECT COUNT(*) FROM ${t};. You decide what the value may hold; BroadSQL does not check it.
• When the statement runs: the value used is the variable's current value. On a line of several statements, such as LET x = 1; SELECT * FROM t WHERE id = ${x};, each statement sees the variables as they are when it runs.
• Once: a value is written into the statement and never read again for ${...}. A value that contains the text ${other} stays that text.
• Undefined is an error: an undefined or misspelt variable, or a malformed reference such as ${ 1}, fails the statement before anything runs or is sent to the database: ERROR: Variable nope is not defined. On a line of several statements, the rest of the line does not run either.
• An empty string is a value: with LET suffix = '';, report${suffix} becomes report.

How each value is written

So WHERE ID = ${id} works for a number, WHERE DAY = DATE '${day}' for a date, and UPDATE T SET A = ${x} sets SQL NULL when x is NULL. Between quotes, NULL is the text NULL. Only the elements of a list are quoted, so that a list is a valid SQL value list: WHERE ID IN ${ids} becomes WHERE ID IN (12, 57, 301) and WHERE COUNTRYCODE IN ${countries} becomes WHERE COUNTRYCODE IN ('FRA', 'DEU'). Where the reference is already inside a value list, no second pair of parentheses is added: IN (${ids}), the form of earlier releases, and IN (${ids}, 400) keep working. A list is written for the database the statement runs on, so a list read on one connection can be used on another. A single text value is never quoted for you. Since a text list carries its own quotes, write it outside quoted text: in ECHO 'Codes ${codes}', its first quote ends the message and the statement fails.

Writing ${ itself

$${ writes ${: $${name} is the text ${name}, left as it is. Only ${name} is a variable reference; $(name) and a $ alone are ordinary text.

Variables in file names and paths

A destination is part of the statement, so it uses variables like the rest:

LET monday = SELECT DATE '2026-10-19';
DUMP CITY TO "Cities ${monday}.FRA" AS XLSX;
DUMP CITY TO "C:\exports\${monday}\cities.FRA" AS XLSX;
REPEAT SELECT COUNT(*) FROM CITY EVERY 1m COUNT 60 TO monitor_${monday}.csv;

The first DUMP writes the worksheet FRA of Cities 2026-10-19.xlsx in the export folder; the second writes into the folder C:\exports\2026-10-19, which must exist. A value is part of the text the command then reads: a value that contains a quote or a space can change how the destination is read, so quote the destination when the value may hold spaces.

RUN and VAR

RUN and VAR also have API variables, written ${name} too. In these two commands a scripting variable is replaced first; a ${name} that is not a scripting variable, and ${ENV:NAME}, are left for the API variables. Everywhere else, ${ENV:NAME} is an error.

In a Script

In a Script, each statement that uses variables is echoed with one line per variable giving the value used, and OUTPUT QUIET hides these lines:

SELECT ID, NAME FROM CITY WHERE ID >= ${id} AND COUNTRYCODE = '${country}'
-- id = 1
-- country = 'FRA'

Scripts from earlier releases

Up to BroadSQL 5.5.0, ${name} in SQL was sent to the database as a separate typed value, never written into the statement, and a string needed no quotes. It is now written into the statement as text, so a Script that used a text variable without quotes must add them. Numbers and lists are unaffected.

Without the quotes, the statement now fails with the database's own error (for example, a column FRA that does not exist): nothing runs with a wrong value. A value that contains an apostrophe ends a SQL string; store it with its apostrophe doubled (LET name = 'O''''Brien';) to use it inside quotes.

Listing variables: SHOW SCRIPT VARIABLES

SHOW SCRIPT VARIABLES lists every variable with its type and value. Strings appear between quotes, so the string 'NULL' is told apart from a NULL value:

After the LET lines above and LET name = 'O''Brien';, it shows:

WORLD> SHOW SCRIPT VARIABLES;
|--------|-------|----------|
|Name    |Type   |Value     |
|--------|-------|----------|
|country |VARCHAR|'FRA'     |
|max_id  |INTEGER|4090      |
|name    |VARCHAR|'O''Brien'|
|--------|-------|----------|

Script variables are unrelated to the API variables set with VAR, which only API requests use. Neither command lists or changes the other's variables.

Variables at the prompt

Variables are not limited to Scripts. They are just as useful when you work interactively:

LET paris = SELECT POPULATION FROM CITY WHERE ID = 2974;

SELECT ID, NAME, COUNTRYCODE, POPULATION
FROM CITY
WHERE POPULATION > ${paris};

SHOW SCRIPT VARIABLES;

Variables belong to the BroadSQL session, not to a connection or a Script:

• The prompt, every Script and every Script it calls share one namespace. A Script sees the variables set at the prompt, and the variables a Script sets with LET remain at the prompt after it ends. Script arguments last only as long as the call.
• A variable gives its value to a Script parameter of the same name that the call does not pass: see Arguments and parameters.
• They survive CONNECT, ENV, DISCONNECT and reconnections, so a value read on one database can be used on another. A LET query always runs on the connection that is current at that moment.
• They are lost when BroadSQL exits.
• / runs the last query again exactly as it was sent, with the values it had then.

ON ERROR and OUTPUT are refused at the prompt; everything else in the Scripting pages works there too.

Related pages

• Scripting overview
• Arguments and parameters: passing values to a Script, as variables.
• Output and error handling: ECHO prints variable values.
• Exporting Script results: ${name} in DUMP and in exported Scripts.
• Command reference: LET, SHOW SCRIPT VARIABLES, VAR.

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