How to use variables for values in SQLite?

Question

I am trying to incorporate variables into a query to make it easier to manage. I followed the guidelines outlined in this answer and tried to implement them.

I am using SQLite for this so I am stuck with its limitations, but it looks like some people have gotten some workarounds to work. The following query runs successfully, but does not return any records. If I replace the variable names in my WHERE clause with 'John' and 'Smith' respectively, the query returns 1 record, but if I use the variable names that I had created instead, 0 records are returned. Removing the quotes around the variables does not make it work.

CREATE TEMP TABLE IF NOT EXISTS _Variables (Name TEXT PRIMARY KEY, Value TEXT);
INSERT OR REPLACE INTO _Variables VALUES ('VarFirstName', 'John');
INSERT OR REPLACE INTO _Variables VALUES ('VarLastName', 'Smith');

SELECT LastName,FirstName,BirthYear,(strftime('%Y', date('now')) - BirthYear) AS Age,LocalExp,TotalExp,TotSalary,TotFringe,WorkLocationName,SchoolName
FROM DPISTAFF2008_2009
WHERE DPISTAFF2008_2009.FirstName = 'VarFirstName' AND DPISTAFF2008_2009.LastName = 'VarLastName'

;DROP TABLE _Variables;

I am assuming that my WHERE clause is not properly referring to the variables I created. What is the proper way to call them? Doing what the answer linked above says does not seem to work for me.

Commenting out ;DROP TABLE _Variables; doesn't change the outcome.

score 2 · Accepted Answer · answered Apr 20 '18 at 11:34

You should be able to select the variables from the table you set up:

SELECT
    LastName,
    FirstName,
    BirthYear,
    (strftime('%Y', date('now')) - BirthYear) AS Age,
    LocalExp,TotalExp,TotSalary,TotFringe,WorkLocationName,SchoolName
FROM DPISTAFF2008_2009
WHERE
    DPISTAFF2008_2009.FirstName =
        (SELECT Value FROM _Variables WHERE Name = 'VarFirstName') AND
    DPISTAFF2008_2009.LastName =
        (SELECT Value FROM _Variables WHERE Name = 'VarLastName');

How to use variables for values in SQLite?

1 Answers1