SQL Procedures, Stored Procedures and Stored Variables: a quick intro

Basic Definitions and Concepts

1. SQL Statement

   The minimal execution unit allowed by SQL syntax; a stand-alone SQL directive to be atomically executed in a single step. Examples:

   SELECT id, name, measure FROM some_table;
   ------------
   DELETE FROM some_table WHERE measure > 1000;
   ------------
   UPDATE some_table SET measure = measure / 10.0
   

   Note: SQL Statements are usually expected to be explicitly terminated by a semicolon (;).
   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 blanks will be always ignored).

2. SQL Script

   A more or less complex sequence of SQL Statements, possibly including interleaved Comments and/or Dot Macros.
   SQL Scripts are intended to be executed in a single monolithic pass (atomic or not, depending on internal Transaction handling). Example:

   .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
   

3. SQL Variable

   A SQL Variable is just a mnemonic symbol enclosed between two special markers (@ or $). Examples:

   @table_name@
   @col_name@
   $value$
   $radius$
   

   Note: using the @ or the $ markers is just an aesthetic choice; both have identical meaning and practical effect.

4. SQL Body

   A SQL Script internally using Variables. Example:

   .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
   

   Note: this second version is obviously more flexible than the initial SQL script, just because there aren't hardwired table-names and column-names, all them are now become symbolic.
   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.
   
   SQL Bodies exist in two different forms:
   • raw: still including symbolic Variables.
   • cooked: presenting all symbols properly replaced by their actual values.
     Rather obviously, you can successfully execute only the cooked form.

5. SQL Procedure BLOB object

   This simply is a BLOB encoded pre-compiled SQL Body, and is the usual target expected to be handled by practically all SQL functions supporting Stored Procedures and alike.

6. Stored Procedure

   A SQL Procedure BLOB object 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).
   Stored Procedures always have the following elements:
   • a name, intended to be an unique identifier (Primary Key).
   • a title, i.e. a short description of the intended scope of the Stored Procedure.
   • a SQL Procedure BLOB Object.

7. Variable with Value strings

   Before executing a SQL Body including Variables you are obviously expected to set some appropriate Value for each Variable, so to allow for replacing Symbols with actual Values (i.e. for properly transforming raw SQL into cooked SQL).
   A Variable with Value string simply is an appropriately well-formatted Text string declaring both the Symbol and the corresponding replacement Value (expressed in its textual representation). Examples:

   '@value_1@=1234'          -- integer value
   '@value_2@=12.34'         -- double value
   '@value_3@=New York City' -- text value
   '@value_4@=x''0123abcd''' -- blob value
   '@value_5@=NULL'          -- null value
   

8. Stored Variable

   A Variable with Value string permanently stored into a database.
   Stored Variables always have the following elements:
   • a name, intended to be an unique identifier (Primary Key).
   • a title, i.e. a short description of the intended scope of the Stored Variable.
   • a Variable with Value string.