Skip to content

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
select * from people where id = {nameOfCell}

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
-- define the variable
{var binSize = 20}

SELECT
    CAST (totalPay / {binSize} AS int) * {binSize} AS bin, count(*) AS count
FROM
    salaries
GROUP BY
    bin

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
{datetime myDate = "2020-10-10"}

SELECT
    {myDate|"The date {0:d} is a {0:dddd}"}

-- the output will be: The date 10/10/2020 is a Saturday

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
{function myFunction(int rowsToReturn = 20)}

select top {rowsToReturn}
    *
from
    HumanResources.Department d

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
{async function myFunction(int rowsToReturn = 20)}
...

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
{functionh myFunction(int rowsToReturn = 20)}
...

...or...

1
2
{async functionh myFunction(int rowsToReturn = 20)}
...