Creating stored procedure and SQLite?

Question

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

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.

If you are still interested, 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

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.

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.