SQLite Forum

Feature request: Stored Procedures
Login
As a long time user of sqlite as a library and a data-mining tool, I'm always for parameterization and tried to add it in one or other way. Recently I suggested using views with parameters at the forum ([https://sqlite.org/forum/forumpost/8f0351af38]), but I doubt this will be implemented. My post also suggested the solution in a form of virtual table and I used it several times since. As I see, you're familiar with user-defined functions so probably with virtual tables too so you would implement something like this.

As a short description, you have a list of named queries in a table and all of them use named or unnamed parameters in the bodies (?1 , :myparameter). The name and the body are stored in the separated fields. The names are registered as eponymous-only modules and when sqlite meets something like
<blockquote>
   Select * from my_parameterized_query(123, 'foo bar') 
</blockquote>

the module responses with a schema containing the input field according to the parameters found in the bodies and the names of the output columns, makes the best cursor negotiating low cost when sqlite provides the required parameters. 

Not surprisingly, this method also allows using parameterized quering "calls" inside other parameterized queries, so they sometimes can be used as "a library" of inter-calling table-valued functions. 

Since new functions are created very easily, they sometimes helps in unexpected ways. I had a cte when I needed a join with the recursive part in a sub-query, which Sqlite currently forbids. But I made a parameterized query for the top-level join accepting the recursive "parameters" and making anything required inside the "body" (for example, group by).