How to Declare and Use Variables in SQLite

SQLite SQL does not have procedural local variables that you declare and assign across statements. In an application, you normally supply changing values through bound parameters. For values reused by several statements on one connection, a temporary table can hold SQL state. A common table expression (CTE) names values for a single statement. These approaches have different scopes, so choose the one that matches how long the value is needed.

Does SQLite support variables?

SQLite supports parameters in prepared statements, but the application or database driver supplies their values. A parameter is a placeholder for a value in a statement; it is not a variable declared by SQL and it cannot stand in for a table or column name. For example, bind a value in SELECT * FROM products WHERE price >= ?. Use the driver’s parameter API to associate the placeholder with the input.

Binding keeps data separate from SQL syntax. It is the right default for values that come from users, configuration, or other application data. To vary a table or column name, select from an application-controlled allowlist and assemble that identifier separately; do not treat it as a bindable value.

Use bound parameters for values

The following Python example uses the standard sqlite3 module to create sample rows and bind a price threshold. It is a complete happy-path example; the SQL placeholder receives a value from Python rather than concatenated text.

Run this example:

import sqlite3

connection = sqlite3.connect(":memory:")
connection.execute("CREATE TABLE products (name TEXT, price REAL)")
connection.executemany(
    "INSERT INTO products (name, price) VALUES (?, ?)",
    [("Notebook", 4.5), ("Keyboard", 25.0), ("Monitor", 180.0)],
)
minimum_price = 20
rows = connection.execute(
    "SELECT name, price FROM products WHERE price >= ? ORDER BY price",
    (minimum_price,),
).fetchall()
for name, price in rows:
    print(f"{name}: {price:.2f}")
connection.close()

A positional placeholder uses ?; named placeholders can use forms such as :minimum and a driver mapping such as {"minimum": 20}. Use the placeholder style and binding method supported by your driver, and bind every input value rather than interpolating it into the SQL string.

Use a temporary table for reusable SQL state

A temporary table is useful when several statements on the same connection need to read or update the same set of values. Create it with CREATE TEMP TABLE settings (name TEXT, value INTEGER), insert rows with ordinary INSERT statements, and query them like any other table. You can update rows between statements and drop the table with DROP TABLE settings when finished.

Temporary tables belong to the connection that created them. They remain available to statements on that connection until they are dropped or the connection closes; another connection does not share them. A transaction controls whether changes commit or roll back, while the temporary table’s connection scope is a separate concern. A temp table models rows of SQL state, rather than a procedural variable declaration, and is unnecessary when a bound parameter can supply the value directly.

Use a CTE for values within one statement

A CTE gives a name to a result used by the statement that follows it. For example, WITH limits AS (SELECT 20 AS minimum_price) SELECT name FROM products, limits WHERE products.price >= limits.minimum_price; defines one value and uses it in the same query. The CTE does not create a variable that later statements can read. Use it to make a query’s inputs or intermediate results easier to read; use a temporary table when the data must remain available across statements on the same connection.

SQLite storage classes and CAST

SQLite values use five storage classes: NULL, INTEGER, REAL, TEXT, and BLOB. These describe the value stored at runtime, not types of declared variables. A column can have a declared type that gives it affinity, which influences how SQLite stores and compares values, but SQLite’s type system is not a set of procedural variable declarations.

CAST(expression AS type) converts an expression according to SQLite’s conversion rules. It applies to that expression’s result; it does not change a parameter, create a variable, or permanently change a value elsewhere. For example, CAST('42' AS INTEGER) expresses the text value as an integer. Conversion can depend on the input and target type, so validate application input when the accepted format matters instead of assuming CAST is input validation. Casting a value is also not a general way to obtain the ASCII code of its characters.

Choose the right approach

Use bound parameters for values supplied by an application, especially user input. They keep values separate from SQL text and work with a single statement without maintaining extra state. Use a temporary table when multiple statements on one connection need a set of reusable or mutable rows. Use a CTE when a value or intermediate result is needed only in one statement. These are complementary tools with different lifetimes, not three ways to declare a persistent SQLite variable.