[sqlite] Creating stored procedure and SQLite?

Is it somehow possible to create a stored procedure, when using 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.

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