I missed stored procedures till i coded my workaround. I had a table named
sprocs with the sql statements coded into a text blob in each row with each row
representing a different sproc. A linked table named params had a column with a
tag representing a parameter for the associated sproc.
Pull the sproc text and the list of the parameters pass that to a prep routine
that replaces the tag text with the relevant values and then execute the sql.
Works like a champ.
Sent from my T-Mobile 4G LTE Device
-------- Original message --------From: Alek Paunov <a...@declera.com> Date:
5/14/18 8:44 PM (GMT-08:00) To: SQLite mailing list
<email@example.com> Subject: Re: [sqlite] Stored Procedures
On 2018-05-09 03:56, Richard Hipp wrote:
> The other benefit of stored procedures is that it provides a way to
> code up a common operation once (correctly!) and store it in the
> database, rather than having multiple clients all have to work out the
> operating themselves (some of them perhaps incorrectly). The usual
> way of handling that in SQLite is to store a script in a text column
> someplace, then execute them as needed. SQLite began life as a TCL
> extension, and so naturally TCL scripts work very well for this kind
> of thing.
To amplify the remark above, for me, the Stored procedures are mostly
method to add methods :-) to the "cold" data objects encoded in tables,
just like the OO style SQLite C API defines interfaces and behavior on
top of "cold" C structures.
That way, schemes and non-trivial calculations on top of them can be
encapsulated and reused as modules (or packages as named in some design
Practical example of the benefit is that e.g. same complex turnover
report which implementation includes bunch of intermediate calculations,
could be used with same SQL call from Python desktop application,
directly as Excel Data Source or on partial replica through SQL.js 
in the browser, without coding the same thing 3 times: on Python, VBA
Having said that, Let's take the survey: How many list readers really
missed the Stored procedures feature?
If there are enough interest, I would be happy to discuss an (possibly
naive) idea (*) for implementation (of simple e.g. Sybase 12.5 level
procedures) mostly in pure SQL with just tiny C runner as UDF TValued
(*) On the sqlite mailing list, we see permanent conflict of interests:
On the one side are 99% of the users, for which sqlite is just better
(than e.g. compressed json) storage option (usually for small as size or
simple as structure data).
On the other, are the advanced users - these with complex applications
who [especially after the great advancements from the last years (CTEs,
JSON and so on)] try to encapsulate as much as possible business logic
into the DB.
My humble opinion is that for us, the minority, the best move is to look
for approaches and collaboration on "incubating" valuable advanced
features initially outside of the SQLite core, then eventually apply
result solutions in our own projects and finally propose for inclusion
in the core library only small key components (which lead to
inefficiencies or maintenance burden when living outside).
For the Stored procedures, borrowing from the SQLite design, I think
that it is possible the non SQL, procedural part of the code to be
translated to very high level VM with just few instructions, which uses
attached :memory: DB for stack/registers/variables and (tree) table for
the code. [This can be seen as step further above the approach cited by
Dr. Hipp SQLs kept in table and used by executed by TCL script.]
That way, at some stage, SQLite could even have several procedural
dialect implementations - TSQL, MySQL, etc. :-)
sqlite-users mailing list
sqlite-users mailing list