Declare variable in SQLite and use it

Question

I want to declare a variable in SQLite and use it in insert operation.

Like in MS SQL:

declare @name as varchar(10) set name = 'name' select * from table where name = @name 

For example, I will need to get last_insert_row and use it in insert.

I have found something about binding but I didn't really fully understood it.

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

Answer 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