Until now SQLite3 does not support Stored Procedures (at least as far as I am aware)
**Are there any plans to introduce also Stored Procedures into SQLite3 ?**
Since many more than 10 years I am using therefore my own extension functions to mimic such a feature. I named my function "call" and its first parameter is the name of the procedure and the other parameters are the inputs to that function.
The call function inserts the parameters into a table with name "stack", which fires a trigger which is watching the name, the first parameter of my call.
The trigger itself can work with all the parameters from the stack record, a helper extension function supports this task.
The trigger itself calls a function return, which can be filled into the stack record but more important becomes the return value of the "call" extended function call and therefore is then the reply into the original sql statement, which caused the trigger to fire.
Because all this happens in scope of the same db context the stack leaves only a trace if there was no delete all from stack happening. I am controlling this for debug purposes, too.
Here is an example for the __recursive__ form of the faculty for number 6 ( = 1 * 2 * 3 * 4 * 5 * 6 = 720 ) , 7! = 5040 , 8! = 40320
~~~
D:\SQLite3x\sandbox>sqlite3 LoadExTest.SQB
SQLite version 3.35.5 2021-04-19 18:32:05
Enter ".help" for usage hints.
sqlite> .load sqlite3.dll
sqlite> .mode box
sqlite> select call('Fakt',6),call('Fakt',7),call('Fakt',8);
┌────────────────┬────────────────┬────────────────┐
│ call('Fakt',6) │ call('Fakt',7) │ call('Fakt',8) │
├────────────────┼────────────────┼────────────────┤
│ 720 │ 5040 │ 40320 │
└────────────────┴────────────────┴────────────────┘
sqlite>
~~~
The stored function itself and the stack table looks like the following:
* Debug() sends output to Windows function OutputDebugString
* argc() returns the count if non-NULL parameters
* argv() returns one parameter from the recent stack table record
* JobLevel() return the recursive depth of calls from the level column
* result() fills the column result and will be the return value for call
~~~
CREATE TABLE stack( job text, -- the function name which will fire the trigger
level integer, inserted float, returned float, -- some debug support
result blob,
par0 blob, par1 blob, par2 blob, par3 blob, par4 blob,
par5 blob, par6 blob, par7 blob, par8 blob, par9 blob );
CREATE TRIGGER Fakt after UPDATE OF job ON stack
FOR EACH ROW WHEN new.job=='Fakt' and argc()>=1
BEGIN
select Debug(concat('JobLevel()=',JobLevel(),': Fakt(',argv(0),')=',
result( argv(0) * case
when argv(0)<2 then 1
else call(new.job,argv(0)-1)
end ) ) );
END;
~~~