How to declare and use variables in SQLite

SQLite has no DECLARE @name like SQL Server, and no session user variables like MySQL. You still need named values in scripts. These are the three ways that actually work.

Bind parameters from your application

This is the right default. Keep the SQL parameterized and let the host language hold the values.

1
SELECT id, name
2
FROM users
3
WHERE created_at >= :since
4
  AND status = :status;

In Go, Node, PHP, or Python you bind :since and :status once and reuse the statement. SQLite never stores those names as database-side variables, which is a good thing: the query plan stays stable and you do not concatenate strings.

sqlite3 CLI parameters

If you are in the sqlite3 shell (3.34+), .parameter is the closest thing to variables:

1
.parameter init
2
.parameter set :user_id 42
3
.parameter set :label 'admin'
4

5
SELECT *
6
FROM users
7
WHERE id = :user_id;

.parameter list shows what you set. These exist only in that CLI session. A connection opened from your app does not see them.

A one-row temp table or CTE

When the SQL has to stand alone (migrations, reports, no host binder), store the values in SQL itself:

1
CREATE TEMP TABLE vars (
2
  user_id INTEGER,
3
  min_score REAL
4
);
5

6
INSERT INTO vars (user_id, min_score) VALUES (42, 0.8);
7

8
SELECT p.*
9
FROM posts AS p
10
JOIN vars AS v ON v.user_id = p.author_id
11
WHERE p.score >= v.min_score;

For a single statement, a CTE is enough:

1
WITH vars AS (
2
  SELECT 42 AS user_id, 0.8 AS min_score
3
)
4
SELECT p.*
5
FROM posts AS p
6
JOIN vars AS v ON v.user_id = p.author_id
7
WHERE p.score >= v.min_score;

What not to do

Do not build SQL by concatenating values. There is no SET @x = 1 that survives across statements on a normal SQLite connection. Use bind parameters from code, .parameter in the CLI, and a temp row or CTE when the SQL must stand alone.