Declare variable in SQLite and use it
Solution 1:
SQLite doesn't support native variable syntax, but you can achieve virtually the same using an in-memory temp table.
I've used the below approach for large projects and works like a charm.
/* Create in-memory temp table for variables */
BEGIN;
PRAGMA temp_store = 2; /* 2 means use in-memory */
CREATE TEMP TABLE _Variables(Name TEXT PRIMARY KEY, RealValue REAL, IntegerValue INTEGER, BlobValue BLOB, TextValue TEXT);
/* Declaring a variable */
INSERT INTO _Variables (Name) VALUES ('VariableName');
/* Assigning a variable (pick the right storage class) */
UPDATE _Variables SET IntegerValue = ... WHERE Name = 'VariableName';
/* Getting variable value (use within expression) */
... (SELECT coalesce(RealValue, IntegerValue, BlobValue, TextValue) FROM _Variables WHERE Name = 'VariableName' LIMIT 1) ...
DROP TABLE _Variables;
END;
Solution 2:
For a read-only variable (that is, a constant value set once and used anywhere in the query), use a Common Table Expression (CTE).
WITH const AS (SELECT 'name' AS name, 10 AS more)
SELECT table.cost, (table.cost + const.more) AS newCost
FROM table, const
WHERE table.name = const.name
SQLite WITH clause
Solution 3:
Herman's solution works, but it can be simplified because Sqlite allows to store any value type on any field.
Here is a simpler version that uses one Value
field declared as TEXT
to store any value:
CREATE TEMP TABLE IF NOT EXISTS Variables (Name TEXT PRIMARY KEY, Value TEXT);
INSERT OR REPLACE INTO Variables VALUES ('VarStr', 'Val1');
INSERT OR REPLACE INTO Variables VALUES ('VarInt', 123);
INSERT OR REPLACE INTO Variables VALUES ('VarBlob', x'12345678');
SELECT Value
FROM Variables
WHERE Name = 'VarStr'
UNION ALL
SELECT Value
FROM Variables
WHERE Name = 'VarInt'
UNION ALL
SELECT Value
FROM Variables
WHERE Name = 'VarBlob';