Using parameters in SQL scripts¶

A parameter is a placeholder you put in a query in place of a literal value, so you can run the same query with different values without rewriting it. In SQLite Expert you can write a parameter in any of the forms SQLite supports:

?
?NNN
:AAAA
@AAAA
$AAAA

The syntax follows the SQLite documentation, with one limitation: SQLite Expert does not support :: occurrences, or suffixes enclosed in (...), in the $AAAA form.

Being prompted for values¶

When a query contains a parameter whose value has not been set, SQLite Expert prompts you for it. For example, running this query against the bundled dbdemos database:

select * from biolife where [Species No] = @SpeciesNo;

prompts you for the value of @SpeciesNo:

The parameter prompt

Setting values with dot commands¶

You can also set parameter values inside the script itself. Normally SQLite Expert passes each line straight to SQLite, but if a line begins with a dot (.), SQLite Expert interprets it as a dot command. Two dot commands control parameters:

Command	Effect
`.set param <name> = <value>`	Sets a parameter to a literal value — a string, integer, floating-point number, BLOB literal, or `NULL`. (A BLOB literal is a hexadecimal string prefixed with `x` or `X`.)
`.reset params`	Clears the values of all parameters.

All parameter values are cleared automatically after the last statement in the script runs.

Example — set the value, so no prompt appears:

.set param @SpeciesNo = 90020;
select * from biolife where [Species No] = @SpeciesNo;

Example — clear it again, so you are prompted:

.set param @SpeciesNo = 90020;
.reset params;
select * from biolife where [Species No] = @SpeciesNo;

Note

Support for parameters via dot commands is experimental, and its syntax may change in a future version.

See also: Executing SQL scripts