LET

Assigns a SQL scripting variable from a literal, another variable, 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

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};

Details

Values

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.

Using a variable

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 and lifetime

• A name is made of letters, digits and _, and is 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.

Related

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

Used in these how-tos

• How do I run a post-deployment smoke check on every environment?
• How do I save a query with parameters and run it by name?
• How do I write a report Script that stops at the first failure?

Last modified in release 5.5.0.

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