Creating stored procedure in SQLite

Question

Is it somehow possible to create a stored procedure when using SQLite?

Answer 1

YES, YOU CAN DO QUITE A LOT to get PROC kind of functions

Option 1 (no return needed or may be result set in version of driver and 'RETURNING *' ):

Create OUTPUT table for christmas tree:

DROP TABLE IF EXISTS XMAS;
CREATE TABLE XMAS ( branch ); 

Create VIEW to define PROC in parameters:

DROP VIEW IF EXISTS myPROC; 
CREATE VIEW myPROC AS SELECT 0 AS height, 'X' AS bar;

Create "INSTEAD OF INSERT" trigger to hold business logic

DROP TRIGGER IF EXISTS myPROC; 
CREATE TRIGGER myPROC  
INSTEAD OF INSERT ON myPROC
FOR EACH ROW
BEGIN
    -- Validate the height
    SELECT RAISE(FAIL, 'Are you a midget, particle physicist, or mole?') WHERE NEW.height < 2;
    SELECT RAISE(FAIL, 'Are you Elon Musk to pay for it?') WHERE NEW.height > 20;
    
    DELETE FROM XMAS;
    
    -- WITH RECURSIVE Tree NOT ALLOWED IN TRIGGER :-(
    INSERT INTO XMAS
    SELECT REPLACE(branch,'x',coalesce(NEW.bar,'x')) FROM Tree_Height_20 LIMIT NEW.height;
    -- Add the trunk
    INSERT INTO XMAS
    SELECT branch FROM Tree_Height_20 LIMIT -1 OFFSET 20 RETURNING *; -- may be returing in some cases
-- or SELECT * FROM XMAS; -- produce result set DOES NOT WORK
END;

NOTE, when using DB Browser you do not see result set from INSERT, but using some driver may be some day you can:

   let sql = "INSERT INTO myPROC VALUES (5,'s') RETURNING *";
   use command = new SQLiteCommand(sql,conn)
   use reader = command.ExecuteReader()
   while reader.Read() do 
      Debug.WriteLine (reader.GetString(0))

may works as supoused! Very like as to Bill Gates time year 1990:

Test it:

INSERT INTO myPROC VALUES (5,'s');
SELECT * FROM XMAS;
branch
                   *
                   s
                  sss
                 sssss
                sssssss
                   B
                   B

and

INSERT INTO myPROC (height) VALUES (3);
SELECT * FROM XMAS;
branch
                   *
                   x
                  xxx
                   B
                   B

NOTE: those do not work correct:

SELECT changes(); -- changes() = ALWAYS 0
SELECT last_insert_rowid(); -- NOT correct - doublecheck it
SELECT * from sqlite_sequence;  works as usual, but may be not when some driver version

Helper - BIG tree to cut from top! Using recursive SQL and CTE:

DROP VIEW IF EXISTS Tree_Height_20;
CREATE VIEW Tree_Height_20 AS
WITH RECURSIVE Tree(level, branch) AS (
    -- Base case: Start with the top of the tree
    SELECT 
        1 AS level, 
        printf('%*s', 20, '*') AS branch
    UNION ALL
    -- Recursive case: Add rows with increasing branches
    SELECT 
        level + 1,
        printf('%*s', 20 - level, '') || substr('xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx', 1, 2 * level - 1) AS branch
    FROM Tree
    WHERE level < 20
)
-- Combine the tree and the trunk into the view
SELECT branch FROM Tree
UNION ALL
SELECT printf('%*s', 20, 'B') -- Trunk row 1
UNION ALL
SELECT printf('%*s', 20, 'B'); -- Trunk row 2

-- SELECT * FROM Tree_Height_20 

Option 2 (return as result set needed):

This is useful when you need dynamically parametrize calculation of result set values in prepared statements:

let sql = """

WITH RECURSIVE p( height, bar) AS (
SELECT 
    coalesce(@height, 5),
    coalesce(@bar,'x')
)   
,Tree(level, branch) AS (
    SELECT 
        1 AS level, 
        printf('%*s', 20, '*') AS branch
        FROM p
    UNION ALL
    SELECT 
        level + 1,
        printf('%*s', 20 - level, '') || substr('xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx', 1, 2 * level - 1) AS branch
    FROM Tree
    JOIN p
    WHERE level < p.height
)
, product('christmas tree') AS (
SELECT branch FROM Tree 
UNION ALL
SELECT printf('%*s', 20, 'B')
UNION ALL
SELECT printf('%*s', 20, 'B')
)
SELECT product.* FROM product JOIN p WHERE p.height BETWEEN 2 AND 20
"""

use conn = new SQLiteConnection(DAL.DtConnectionString)
conn.Open()
use command = new SQLiteCommand(sql,conn)
command.Parameters.Add(SQLiteParameter("@height", 6)) |> ignore 
command.Parameters.Add(SQLiteParameter("@bar", "s")) |> ignore 
use reader = command.ExecuteReader()
while reader.Read() do Debug.WriteLine (reader.GetString(0))

will print this:

               *
               x
              xxx
             xxxxx
            xxxxxxx
           xxxxxxxxx
               B
               B

Key here are those lines:

SELECT 
    coalesce(@height, 5),
    coalesce(@bar,'x')
)   

Typical SQL do not allow to use dynamic parameters like '@height' in list of

SELECT ...  

or some other uncommon places:

SELECT substr(DTMI,@a), * FROM DigitalTwin ORDER BY @b LIMIT @c OFFSET @d"

Conclusion: with SQLite you can do lot of things, even TEMPORAL TABLES, wiht Sys-Start-End-Time etc. but it is not fun, when you are not senior developer from 1990.s

and when you just can accept "funny syntax"

SELECT * FROM INSERT INTO myFakeView VALUES (1,2)...

instead

EXEC myProc(1,2)

you can be happy. But do not promise to your boss to convert code to SQL Server or Oracle! Let some junior do it.

Answer 2

One more compact SP to insert cartesian X,Y to polar R,A table

in classic SQL one will CREATE EXEC toPolar(id,X,Y) but in SQLite you can:

create target polar view/table and UPSERT trigger

CREATE TABLE polar_shadow ( id INT PRIMARY KEY, r, a);
CREATE VIEW polar AS SELECT * FROM polar_shadow;
DROP TRIGGER polar_UPSERT;
CREATE TRIGGER polar_UPSERT
INSTEAD OF INSERT ON polar
FOR EACH ROW
BEGIN
    INSERT INTO 
    VALUES (
        NEW.id,
        NEW.r,
        NEW.a
    )
    ON CONFLICT(id) DO UPDATE 
    SET 
        r = NEW.r,
        a = NEW.a;
END;

and "Stored Procedure"

-- CREATE PROC toPolar(id,x,y) AS BEGIN
-- UPSERT polar VALUES ( 1, sqrt(x**2 + y**), artan(y/x)) END
-- use as: EXEC toPolar(1,5,15)

CREATE VIEW toPolar_PROC AS SELECT 0 id, 0 x, 0 y;

DROP TRIGGER toPolar_PROC;
CREATE TRIGGER toPolar_PROC
INSTEAD OF INSERT ON toPolar_PROC
FOR EACH ROW
BEGIN
    INSERT INTO polar 
    WITH CTE AS ( SELECT 
        NEW.id, 
        sqrt(NEW.x * NEW.x + NEW.y * NEW.y), 
        atan2(NEW.y, NEW.x) 
    )
    SELECT * FROM CTE;
END;

Test it:

-- intead use: EXEC toPolar(1,5,15)
-- use

INSERT INTO toPolar_PROC VALUES(1,0,0);
INSERT INTO toPolar_PROC VALUES(2,1,1);
INSERT INTO toPolar_PROC VALUES(2,10,1);

SELECT * FROM polar;

produces

id  r   a
1   0.0 0.0
2   10.0498756211209    0.099668652491162

Note: I do use powerful Common Table Expression for complex business logic:

   INSERT INTO target 
   WITH 
   ...
   ... business logic using relational SQL power.
   ...
   ... when failure like:
   ... UNION ALL SELECT RAISE(FAIL, 'optimistc lock error ...') 
   ...     WHERE ValidFrom <> coalesce(NEW.ValidFrom,ValidFrom) etc.
   ...
   SELECT * FROM CTE; 

Answer 3

SQLite does not support it natively. But...

I created Stored Procedures for SQLite

Here is an example procedure:

CREATE PROCEDURE add_new_sale(@products) BEGIN 
 INSERT INTO sales (time) VALUES (datetime('now'));
 SET @sale_id = last_insert_rowid();
 FOREACH @prod_id, @qty, @price IN @products DO 
   INSERT INTO sale_items (sale_id, prod_id, qty, price) VALUES (@sale_id, @prod_id, @qty, @price);
 END LOOP;
 RETURN @sale_id;
END;

And how to call it:

CALL add_new_sale(ARRAY( ARRAY('DJI Avata',1,1168.00), ARRAY('iPhone 14',1,799.90), ARRAY('iWatch',2,249.99) ));

Answer 4

I've come across this question myself. I think stored procedures are supported in PHP PDO, but that module is handling it and building normal SQL queries to send to SQLite. So, in PHP, possible to write stored procedures in your code, but no performance gain from using them.

Correct me if I'm wrong, please.

Answer 5

Chris Wolf made a prototype implementation of SQLite with stored procedures. You can find the details at his blog post: Adding Stored Procedures to SQLite

Answer 6

No, but you can :

• Write long multi-statement scripts
• Create temporary one-row tables called e.g. Vars to hold variables
• Create a View over a Recursive CTE to program arbitrary functions in pure SQL queries.

So you can do most things you would normally do with stored procs.

For how to program functions in a SQL View see https://www.cafe-encounter.net/p3300/pretending-that-sqlite-has-stored-procedures-and-functions.

Alternatively you can:

• Compile short single-page C programs to program arbitrary functions

This is easier and less work than you might think!

A step-by-step guide is at https://www.cafe-encounter.net/p3244/installing-and-using-sqlite-extensions-on-macos-and-maybe-windows-linux-too . This does add some deployment work: you will have to deploy the additional dll/so/dylib files with your application.

Answer 7

Yet, it is possible to fake it using a dedicated table, named for your fake-sp, with an AFTER INSERT trigger. The dedicated table rows contain the parameters for your fake sp, and if it needs to return results you can have a second (poss. temp) table (with name related to the fake-sp) to contain those results. It would require two queries: first to INSERT data into the fake-sp-trigger-table, and the second to SELECT from the fake-sp-results-table, which could be empty, or have a message-field if something went wrong.

Answer 8

Answer: NO

Here's Why ... I think a key reason for having stored procs in a database is that you're executing SP code in the same process as the SQL engine. This makes sense for database engines designed to work as a network connected service but the imperative for SQLite is much less given that it runs as a DLL in your application process rather than in a separate SQL engine process. So it makes more sense to implement all your business logic including what would have been SP code in the host language.

You can however extend SQLite with your own user defined functions in the host language (PHP, Python, Perl, C#, Javascript, Ruby etc). You can then use these custom functions as part of any SQLite select/update/insert/delete. I've done this in C# using DevArt's SQLite to implement password hashing.

Answer 9

SQLite has had to sacrifice other characteristics that some people find useful, such as high concurrency, fine-grained access control, a rich set of built-in functions, stored procedures, esoteric SQL language features, XML and/or Java extensions, tera- or peta-byte scalability, and so forth

Source : Appropriate Uses For SQLite