SQLite is marvellous. The fact that it doesn't have SQL syntax for stored procs and functions is usually not a handicap because it has an interface for the consuming application to register functions, which means you get to write your functions in your preferred programming language. Win all round.

But sometimes you do wish you could do it in SQL.

The lack of Stored Procedures is usually ok—you can just use scripts. Variables are easy to do: create a one-row temporary table and call it `args`

or `var`

. Working around the lack of Functions seems harder but in fact, you can program functions with Views. You can use CTEs in a View definition, so you can build up complex calculations. And CTEs allow recursion so you have Turing completeness.

As an example, the Exponent function as a View:

```
Drop Table if Exists Args ; Create Table Args as Select 5.5 as Base, 4 as Exponent ;
Drop View If Exists Power;
Create View Power As
WITH RECURSIVE pow(exponent, exponent_remainder, base, result) as (
--FIRST EXPRESSION
SELECT exponent, exponent-1 , base, base
FROM Args
union all
--SECOND EXPRESSION
select Args.exponent, pow.exponent_remainder -1, pow.base, pow.result * pow.base
from Args
join pow on Args.exponent = pow.exponent
where pow.exponent_remainder >= 0
)
select pow.result
from pow
where pow.exponent_remainder = 0;
```

and now you ‘call the function’ with:

`Update Args set Base=2.5, Exponent=5; Select Result from Power;`

The elements of the workaround are:

- A one-row table for function arguments
- A view which can refer to the arguments table and do the calculation. Since you can use CTEs to do recursion, you could in principle programming anything this way.

In similar style, here's an Exponential function which lets you specify how many significant digits you want the result to, default to about 7 digits of accuracy. This time we call the Args (X,Y,Z,p4,p5,…)

```
Drop Table if Exists Args ;
Create Table Args as Select 1 as X, 2 as Y, 3 as Z, 4 as p4, 5 as p5, 6 as p6;
Drop View If Exists Exp;
Create View Exp As
WITH RECURSIVE exp1(X, N, term, approx, accuracy ) as (
--FIRST EXPRESSION
SELECT X, 1, X, 1+X, Max(Min(Y, 1),0.00000000000000001) FROM Args
Union All
--SECOND EXPRESSION
Select X, N + 1, term * X / (N + 1), approx + term * X / (N + 1), accuracy
From exp1
Where term / approx > accuracy Or N <3
)
Select approx as Result From exp1 Order By N Desc Limit 1;
```

And then:

```
Update Args Set X=22.0, Y=0.00000000000001;
Select * from Exp;
#
# > 3584912846.1315813 # Exp(22) correct to 14 digits
```

#### Links

- SQLite CTEs https://sqlite.org/lang_with.html
- Silvano González's answer on https://stackoverflow.com/questions/13190064/how-to-find-power-of-a-number-in-sqlite/13190799#13190124

## One thought on “Pretending that SQLite has Stored Procedures and Functions”