Declare a variable in SQLite and use it

Question

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

Like in MS SQL:

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

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

I found something about binding, but I did not quite understand it.

Answer 1

SQLite does not support native variable syntax, but you can accomplish pretty much the same thing using the temp in-memory temp table.

I used the approach below for large projects and works like a charm.

  /* Create in-memory temp table for variables */ BEGIN; PRAGMA temp_store = 2; 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

Herman's solution works, but it can be simplified because Sqlite allows you to store any type of value in any field.

Here is a simpler version that uses a single 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'; 

Answer 3

Herman's solution worked for me, but ... I got mixed up a bit. I included the demo I was working on based on his answer. Additional features in my answer include foreign key support, auto-increment keys, and using the last_insert_rowid() function to get the last automatically generated key in a transaction.

My need for this information arose when I came across a transaction that required three foreign keys, but I could only get the last with last_insert_rowid() .

 PRAGMA foreign_keys = ON; -- sqlite foreign key support is off by default PRAGMA temp_store = 2; -- store temp table in memory, not on disk CREATE TABLE Foo( Thing1 INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL ); CREATE TABLE Bar( Thing2 INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, FOREIGN KEY(Thing2) REFERENCES Foo(Thing1) ); BEGIN TRANSACTION; CREATE TEMP TABLE _Variables(Key TEXT, Value INTEGER); INSERT INTO Foo(Thing1) VALUES(2); INSERT INTO _Variables(Key, Value) VALUES('FooThing', last_insert_rowid()); INSERT INTO Bar(Thing2) VALUES((SELECT Value FROM _Variables WHERE Key = 'FooThing')); DROP TABLE _Variables; END TRANSACTION; 

Answer 4

Try using binding values. You cannot use variables like in T-SQL, but you can use "parameters". I hope the following link is helpful. Binding Values

Answer 5

For read-only variables (using the same value in the query), use a shared table expression (CTE).

 WITH var AS (SELECT 'name' AS name, 10 AS more) SELECT *, (table.cost + var.more) AS result FROM table, var WHERE table.name = var.name 

Sqlite clause