On this page

LET

Core command

Assigns a SQL scripting variable from a literal or a one-row query.

Syntax

LET <name> = <value>

Quick start

LET customer_id = 42;
SELECT * FROM orders WHERE customer_id = ${customer_id};

Sets customer_id, then uses it as a value in a query.

Arguments

Argument	Required	Values	Default
`<name>`	Yes	Letters, digits and `_`, in any case; not `ENV`, `NULL`, `TRUE` or `FALSE`	None
`<value>`	Yes	A number, a single-quoted string, `TRUE`, `FALSE`, `NULL`, `${other}`, or a `SELECT`, `WITH` or `VALUES` query returning one row and one column	None

Examples

LET customer_id = 42;
LET country = 'FR';
LET active = TRUE;
LET saved_id = ${customer_id};
LET label = 'Country ${country}';
LET max_id = SELECT MAX(id) FROM customer;
SELECT * FROM orders WHERE customer_id = ${max_id} AND country = '${country}';

Details

Values

The value is a literal or a query:

a number (42, -1.5), a single-quoted string ('O''Brien'), TRUE, FALSE or NULL;
a query starting with SELECT, WITH or VALUES: it runs on the current connection, in the current transaction, and must return exactly one row and one column. Zero rows, several rows or several columns are errors; a NULL value is assigned as NULL. The query result is never displayed and never becomes the last result.

Using a variable

${name} anywhere in a later statement is replaced by the variable's value, as text, when that statement runs: SELECT * FROM orders WHERE customer_id = ${id};, SELECT * FROM T WHERE A = '${name}';, DUMP T TO report_${day} AS XLSX;, CONNECT ${target};. Nothing is added: write the quotes the SQL needs. To copy a variable, write its value as a literal: LET y = ${n}; for a number, LET y = '${s}'; for a string. LET holds single values: a list is assigned and copied with LIST (LIST codes = 'FRA', 'DEU';, LIST saved = ${codes};).

Names and lifetime

A name is made of letters, digits and _, and is case-insensitive. ENV, NULL, TRUE and FALSE are reserved.
A name holds either a single value or a list: LET refuses a name that holds a list (LIST).
Variables belong to the BroadSQL session: Scripts and the prompt share them, they survive CONNECT and ENV, and they are lost when BroadSQL exits.
A failed assignment leaves the variable unchanged.

List them with SHOW SCRIPT VARIABLES. LET is unrelated to VAR, which sets API variables.

LIST, lists
SHOW SCRIPT VARIABLES
ECHO
@, named arguments
VAR, the API variables
Script variables

Used in these how-tos

Last modified in release 5.5.5.

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

LET