<table cellspacing="12" width="100%">
<tr><td colspan="2">
<table width="100%" bgcolor="#f0f0f8">
<tr><td align="center">

<table width="100%"><tr>
<td width="33%" align="left"></td>
<td align="center"><a to
<td width="33%" align="right"></td>

<h1>SQL Procedures, Stored Procedures and Stored Variables: a quick intro</h1>
Starting since version <b>4.5.0</b> SpatiaLite supports an advanced mechanism intended to significantly extend the scripting techniques allowing for a powerful and flexible automation of complex workflows.<br>
Think of the nice flexibility allowed by mixing standard <b><i>SQL and to be replaced just before actual execution.
Not only: think of many wonderful opportunities deriving from permanently storing within a database such <b><i>SQL Scripts with so to make as simple as possible automating common workflows to be repeatedly executed.
This exactly is the intended scope of this new module.<br><br>
We'll start first by formally defining few basic concepts and objects so to avoid any possible confusion.<br><br>
<hr>
<h2>Basic Definitions and Concepts</h2>
<ol>
<li>
<h4>SQL Statement</h4>
The minimal execution unit allowed by SQL syntax; a stand-alone SQL directive to be atomically executed in a single step. Examples:
<verbatim>
SELECT id, name, measure FROM some_table;
------------
DELETE FROM some_table WHERE measure > 1000;
------------
UPDATE some_table SET measure = measure / 10.0
</verbatim>
<u>Note</u>: SQL Statements are usually expected to be explicitly terminated by a
The semicolon terminator can be safely suppressed only when the SQL Statement is immediately followed by a string terminator (leading and trailing sequences of all will be always ignored).
</li>
<li>
<h4>SQL Script</h4>
A more or less complex sequence of <b><i>SQL possibly including interleaved and/or <b><i>Dot
SQL Scripts are intended to be executed in a single monolithic pass (atomic or not, depending on internal handling). Example:
<verbatim>
.echo on

--
-- starting a Transaction
--
BEGIN;

--
-- creating a table
--
CREATE TABLE example (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  name TEXT NOT NULL,
  value DOUBLE);

--
-- populating the table
--
INSERT INTO example (id, name, value) VALUES (NULL, 'first', 12.34);
INSERT INTO example (id, name, value) VALUES (NULL, 'second', 56.78);
INSERT INTO example (id, name, value) VALUES (NULL, 'third", NULL);

--
-- committing the pending Transaction
--
COMMIT;

.quit
</verbatim>
</li>
<li>
<h4>SQL Variable</h4>
A <b><i>SQL is just a <i><u>mnemonic enclosed between two <i><u>special or Examples:
<verbatim>
@table_name@
@col_name@
$value$
$radius$
</verbatim>
<u>Note</u>: using the or the markers is just an <i><u>aesthetic both have identical meaning and practical effect.
</li>
<li>
<h4>SQL Body</h4>
A <b><i>SQL internally using Example:
<verbatim>
.echo on

--
-- starting a Transaction
--
BEGIN;

--
-- creating a table
--
CREATE TABLE @table@ (
  @pk@ INTEGER PRIMARY KEY AUTOINCREMENT,
  @col1@ TEXT NOT NULL,
  @col2@ DOUBLE);

--
-- populating the table
--
INSERT INTO @table@ (@pk@, @col1@, @col2@) VALUES (NULL, 'first', 12.34);
INSERT INTO @table@ (@pk@, @col1@, @col2@) VALUES (NULL, 'second', 56.78);
INSERT INTO @table@ (@pk@, @col2@, @col2@) VALUES (NULL, 'third", NULL);

--
-- committing the pending Transaction
--
COMMIT;

.quit
</verbatim>
<u>Note</u>: this second version is obviously more flexible than the initial SQL script, just because there aren't table-names and column-names, all them are now become
So we are now free to reuse more times this SQL Body in different contexts; we just have to appropriately set the Variable Values just immediately before actual execution.<br><br>
<b><i>SQL exist in two different forms:
<ul>
<li><b>raw</b>: still including symbolic Variables.</li>
<li><b>cooked</b>: presenting all symbols properly replaced by their actual values.<br>
Rather obviously, you can successfully execute only the form.</li>
</ul>
</li>
<li>
<h4>SQL Procedure BLOB object</h4>
This simply is a <b><i>BLOB pre-compiled <b><i>SQL and is the usual target expected to be handled by practically all SQL functions supporting Stored Procedures and alike.
</li>
<li>
<h4>Stored Procedure</h4>
A <b><i>SQL Procedure BLOB permanently stored into a database. This will make easier reusing more times the same SQL workflow, perhaps so to implement routinely periodic tasks (to be executed daily / weekly / monthly and so on).<br>
Stored Procedures always have the following elements:
<ul>
<li>a <b>name</b>, intended to be an unique identifier (<b><i>Primary
<li>a <b>title</b>, i.e. a short description of the intended scope of the Stored Procedure.</li>
<li>a <b>SQL Procedure BLOB
</ul>
</li>
<li>
<h4>Variable with Value strings</h4>
Before executing a <b><i>SQL including you are obviously expected to set some appropriate for each Variable, so to allow for replacing with actual (i.e. for properly transforming <b>raw</b> SQL into <b>cooked</b> SQL).<br>
A <b><i>Variable with string simply is an appropriately well-formatted Text string declaring both the Symbol and the corresponding replacement Value (expressed in its textual representation). Examples:
<verbatim>
-- integer value
-- double value
York City' -- text value
-- blob value
-- null value
</verbatim>
</li>
<li>
<h4>Stored Variable</h4>
A <b><i>Variable with Value permanently stored into a database.<br>
Stored Variables always have the following elements:
<ul>
<li>a <b>name</b>, intended to be an unique identifier (<b><i>Primary
<li>a <b>title</b>, i.e. a short description of the intended scope of the Stored Variable.</li>
<li>a <b>Variable with Value
</ul>
</li>
</li>
</ol><br>
<hr>


<br><br>
<table width="100%"><tr>
<td width="33%" align="left"></td>
<td align="center"><a to
<td width="33%" align="right"></td>