Does SQLite support stored procedures?

Question

I am evaluating the SQLite database for my requirement. Does SQLite support stored procedures?

If yes then what are the limitations? Does SQLite support the range?

Answer 1

No, it does not. See Appropriate Uses For SQLite on the main site.

Answer 2

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 run as a DLL in your current process it makes more sense to implement SP in the client language.

You can however extend SQLite with your own user defined functions in the host language (PHP, Python, Perl, C#, Javascript, Ruby etc). I've done this in C# using DevArt's SQLite to implement password hashing.

Answer 3

If you really want to store SQL code in the DB (such as when you want to develop cross-platform apps), you can create a specific table that will store raw SQL commands that do the thing, then in the client you obtain the SQL command. e.g.

var myString = db.CreateCommand("SELECT SqlColumn FROM tablewithsqlcommands WHERE Procname=theprocedureIwant").ExecuteScalar();

and then execute it in the second step

var myResult = db.CreateCommand(myString).whatever_execution_method_you_need();

Answer 4

No, but you can use this:

Stored Procedures for SQLite

Here is how to create a procedure:

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

And how to call it:

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

Note: I am the creator