BroadSQL BroadSQL Downloads
Documentation / Commands / LET

LET

Core command

Assigns a SQL scripting variable, at the prompt or in a Script. The value is a number, a single-quoted string, TRUE, FALSE, NULL, one variable reference ${other} (an exact copy), or a query starting with SELECT, WITH or VALUES that returns exactly one row and one column. Use the variable in SQL as ${name}: it is sent as a typed parameter, never pasted into the SQL text. Variables are shared by the prompt and every Script of the session. List them with SHOW SCRIPT VARIABLES. LET is unrelated to VAR, which sets API variables used by API requests only.

Arguments

<name> = <value> (mandatory) a name made of letters, digits and _, then a literal, ${other}, or a query

Examples

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

Notes

Assigns a SQL scripting variable: LET <name> = <value>, at the prompt or in a Script.

The value is a literal, a copy of another variable, or a query:

  • a number (42, -1.5), a single-quoted string ('O''Brien'), TRUE, FALSE or NULL;
  • ${other}: an exact copy of another variable, same value and same type;
  • 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.

Use the variable as a SQL value with ${name}: SELECT * FROM orders WHERE customer_id = ${id};. The value is sent to the database as a typed parameter, never pasted into the SQL text, so an apostrophe needs no escaping and a value can never change the statement. Names are case-insensitive; ENV, NULL, TRUE and FALSE are reserved.

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.

See Script variables in the SQL scripting guide for examples.

Last modified in release 5.3.3.

← All commands