1. Forgot to mention. In cases where the WHEN clause is not convenient for trigger style stored procedure condition branching there is also "SELECT raise(ignore) WHERE <condition>":
https://sqlite.org/lang_createtrigger.html#raise 2. Here is a question. It would be helpful to know if TRIGGERs are stored as prepared SQLite byte code or not. What does the SQLite engine do exactly? Anybody? If CREATE TRIGGER produces prepared byte code, then TRIGGER programs are not equivalent to making your own table of stored procedures in TEXT columns that have to be loaded by external code which repeatedly issues the prepare statement call. 3. For variables compare, "UPDATE mysproc_worktable SET name='John'" with "LET @name='John'". The difference amounts to a lack of imagination. 4. Those requiring loop constructs should consider that TRIGGERs are re-entrant and can be called recursively. Any loop can be written as a recursive call. 5. Recursive CTE's are also available to directly generate/populate the result columns of the worktable. In short, SQLite has a fairly complete defacto stored procedure capability that many could benefit from. But either for lack of a direct CREATE PROCEDURE statement or a profound lack of imagination, many will never use it. On Wed, Apr 19, 2017 at 2:22 PM, R Smith <rsm...@rsweb.co.za> wrote: > > > On 2017/04/19 6:58 PM, James K. Lowden wrote: > >> On Sun, 16 Apr 2017 12:01:01 +0200 >> Darko Volaric <li...@darko.org> wrote: >> >> There are good reasons to have stored procedures other than reducing >>> connection latency - developers like to encapsulate logic that is >>> associated entirely with the database in the database, use them to do >>> extended checking, to populate denormalized or derived data, or to >>> provide a level of abstraction, for instance. >>> >> Exactly so. A stored procedure can serve the same purpose as a >> function in C: to assign a name to a particular body of code. >> >> But the same effect can be had in SQLite without stored procedures per >> se. In a few projects I've used the build repository to accomplish >> much the same thing.//.... >> > > Indeed so, and I've had good success using a similar principle by simply > storing those SQL "files" as simple TEXT column in a table named > "StoredProcs" in any DB with a trivial step in the program to execute it > when needed - thus truly having "Stored Procedures" by virtue of placement. > > However, I believe the main motivation of the requests do not intend the > placement of the procedures so much as the character thereof - They do not > care whether it is stored in a Trigger, File or Table, I believe the real > request is for a system of assignable variables ( LET @Name = 'John'; ) > which could also be used as parameters in a query, or maybe assignable > datasets ( #TmpResult = Query('...'); ) - perhaps even traversable datasets > ( for each @Row in Query('...') do { ... DoSomethingWith( @Row.Name ); ... > } ) and next will be flow control ( IF (thisIsTrue) BEGIN doThat(); END ). > > Once we start on this road, ALL those will become wanted - all of which > are great, but probably outside the spirit of SQ"Lite". > > (Note: I'm not advocating against. I myself am on the fence - using SQLite > so much and never in a size-sensitive anything, so it would be a boon to > have proper procedural execution within, but a "general target audience" I > don't make.) > > _______________________________________________ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
