How to set parameters from within SQLite?

Question

I've been unable to figure out how to set parameters from within SQLite. I'm mainly using it for testing, and am designing my code so that it can be ported as easily as possible to another, yet to be determined, version of SQL. Normally, I use named parameters, and when testing queries internally, I will use the syantax @varname="value". I'll provide two examples below, showing how I would like to use parameters when testing.

This first example just sets and displays a variable.

@string="Example String";
SELECT @string;

This should return the contents of the variable, in this case: example string.

In the second example, the parameter is being used as part of a condition:

@param=Dark;
SELECT Title from table;
WHERE Title= '%' || @param || '%';

This should return results like the following:

Afterdark    
Alone in the Dark
The Darkness

etc.

Answer 1

SQLite is an embedded database, and designed to be used together with a 'real' programming language.

The only way to set parameter values is from outside SQL.

Answer 2

User-defined variables can be defined in SQLite using a CTE:

Example 1

WITH vars as (SELECT "Example String" as string)
SELECT vars.string FROM vars;

Example 2

WITH vars as (SELECT "Dark" as param)
SELECT Title from [table], vars
WHERE Title LIKE '%' || vars.param || '%';

See also https://stackoverflow.com/a/56179189/997358

Answer 3

The below SQLite script will give the required output:

BEGIN;

    CREATE TEMP TABLE _Titles(Title TEXT);

    /* Declaring a variable */
    INSERT INTO _Titles(Title) VALUES ('dark');

    /* Getting variable value (use within expression) */
    SELECT Title FROM TitleTable WHERE Title like ( '%' || (SELECT Title FROM _Titles WHERE Title = 'dark' LIMIT 1) || '%');

    DROP TABLE _Titles;
END;

Please refer: Declare variable in sqlite and use it