Declare variable in SQLite and use it

by

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;

More Related Contents:

  1. How to declare variable and use it in the same Oracle SQL script?
  2. Selecting more than one value
  3. Is it possible to insert multiple rows at a time in an SQLite database?
  4. SQLite Reset Primary Key Field
  5. Update table values from another table with the same user name
  6. How do you use script variables in psql?
  7. How do I dump the data of some SQLite3 tables?
  8. SQLite – How do you join tables from different databases?
  9. Does SQLite3 not support foreign key constraints?
  10. SQLite – ORDER BY RAND()
  11. Query to get records based on Radius in SQLite?
  12. How do I add a foreign key to an existing SQLite table?
  13. How to use SQL Order By statement to sort results case insensitive?
  14. How to return the output of stored procedure into a variable in sql server
  15. Varchar variable is not working in WHERE clause
  16. Sqlite: CURRENT_TIMESTAMP is in GMT, not the timezone of the machine
  17. sqlite alter table add MULTIPLE columns in a single statement
  18. Is there AUTO INCREMENT in SQLite?
  19. Drop all tables command
  20. Get month from DATETIME in sqlite
  21. How do I import .sql files into SQLite 3?
  22. sqlite equivalent of row_number() over ( partition by …?
  23. String concatenation does not work in SQLite
  24. Android multiple databases open
  25. Unfindable SQLite Syntax Error
  26. SQLite3 UNIQUE constraint failed error
  27. Does SQLite3 have prepared statements in Node.js?
  28. Coldfusion – variable field name when looping through database query results
  29. SQlite: select into?
  30. Copy table structure to new table in sqlite3

Leave a Comment