The SQL Preprocessor
QueryStorm introduces a simple preprocessor that's available in all SQL scripts in QueryStorm. The job of the preprocessor is to allow:
- Using values from workbook cells in SQL code
- Defining Excel functions
- Defining Excel event handlers
- Defining query classes that can be used from .NET code
Inserting values into SQL
The preprocessor can be used to insert values from Excel cells into SQL code, for example:
1 |
|
The value of the named cell will be inserted instead of the {nameOfCell}
placeholder before the query is passed to the SQL engine. This is a purely textual operation. If the inserted value is a string or a date, it is automatically quoted.
To prevent quoting, add an equals sign before the name of the cell: {=nameOfCell}
. This allows inserting raw SQL code into the query (SQL injection), so caution is advised when doing this.
User defined variables
Users can also define variables in code. This is especially useful when working with database engines that don't natively support defining variables (e.g. SQLite, Access).
A variable can be defined and referenced in the following way:
1 2 3 4 5 6 7 8 9 |
|
Formatting values before insertion
Values can be formatted before inserting them into the query. The syntax for formatting is as follows: {variableName|formatSpecifier}.
The format specifier is a standard format specifier for the .NET string.Format method.
For example:
1 2 3 4 5 6 |
|
Preprocessor expressions vs parameters
SQL scripts can reference named cells as parameters (if they support named parameters), so inserting values into the query text with the preprocessor usually isn't required.
However, there are several reasons why this might be useful:
- It allows defining and referencing variables with engines that do not natively support user-defined variables (e.g. SQLite)
- It allows formatting values before inserting into SQL code
- It allows using cell values as parameters when working with databases that do not support named parameters (e.g. ODBC, Access)
- It allows injecting raw SQL into queries
Defining Excel functions
The second important task of the preprocessor is declaring functions that can be used from Excel. The body of the function is written in SQL and the declaration of the function via the preprocessor.
For example:
1 2 3 4 5 6 |
|
The above query can be used to define an Excel function that accepts a single parameter and returns a table of results. For more information on creating functions with SQL, click here.
Async functions
Functions can be declared as asynchronous, meaning that Excel will not block while they are executing. To make a function asynchronous, simply use the async
keyword, for exmaple:
1 2 |
|
Functions with headers
Functions can optionally include headers in the results. To include headers, use the functionh
keyword instead of the function
keyword, for example:
1 2 |
|
...or...
1 2 |
|