Excel functions via SQL

QueryStorm allows creating Excel functions that use SQL to return data from a database. The body of the function is written in SQL, while the declaration of the function uses a preprocessor syntax that's specific to QueryStorm.

Example

Suppose we'd like to define a function that connects to a database and returns a list of people whose names contain a specified search term.

To define this function, we need to create a new script and connect to our target database. Once connected, we can use the following code to define and test the function:

-- the declaration of the function
{function searchPeople(string searchTerm = "tim")}

-- the body of the function
select
    *
from
    Person.Person p
where
    FirstName like '%' + @searchTerm + '%'

We can run the code to examine if it returns the expected results. When running the query, the default value "tim" will be used as the value of the @searchTerm parameter.

Running the code simply runs the query, but does not yet register the function with Excel. To register this function with Excel, we must:

1. Save the script Ctrl+S
2. Build the project Ctrl+Shift+B

This will build the project creating an app. The app is then loaded by the QueryStorm runtime and the functions it contains are registered with Excel.

For a video demonstration of the process, click below:

Function naming

The name of the function can contain alphanumeric characters, underscores and dots. Using dots in the name is useful to ensure the function name is unique as well as to group together related functions so they appear next to each other in the completion list.

For example, functions that read data from an AdventureWorks database could all have the same prefix, e.g. advWks.GetDepartments, advWks.GetEmployees, advWks.GetSalesTransactions...

Function parameters

Functions can accept zero or more parameters. In case of multiple parameters, the parameter declarations should be separated by a comma character. Any whitespace characters in the function declaration are ignored.

{function advWks.ListTransactions(string category = "tyres", int year = 2021)}

A parameter declaration consists of the name, type, and default value. The following are examples of valid parameter declarations:

• int abc = 123
• float abc = 12.3
• datetime abc = "2020-08-31" (ISO 8601 format)
• string abc = "some text"
• bool abc = true
• var abc = "example text" (infer data type)

Parameter data types

There are five supported data types for parameters: int, float, datetime, string, and bool. In addition to the five explicit data types, the var keyword can be used to let the preprocessor infer the data type.

Referencing parameters

Parameters can be referenced in the body of the SQL query in two ways:

• via preprocessor expressions
• as regular SQL parameters

Preprocessor expressions are evaluated and replaced with their value before passing the SQL command to the database engine. For example:

{function advWorks.getDepartments(int maxRows = 5)}
select top {maxRows} * from HumanResources.Departments

In the example above, if the value 10 was passed as the maxRows parameter, the following query would be sent to the database:

select top 10 * from HumanResources.Departments

Parameters are also available using the regular SQL parameter syntax. For example:

{function advWorks.getSales(int year = 2021)}
select * from Sales.SalesOrderHeader where year(orderDate) = @year

In the example above, the query is sent to the database as-is, but the year parameter is added as a named parameter to the SQL command.

Parameter default values

A parameter's default value is used when the script is manually executed. This is useful for testing the query before defining the function.

The default values is also used when the function is called from an Excel formula, in case the user did not supply a value for the parameter.

Return values

Functions can return a single value or an entire table as the result.

If your machine (or the end user's machine) is running one of the newer (Office365) versions of Excel that support dynamic arrays, tabular results will automatically spill.

If you are using an older version of Excel, you will need to use Ctrl+Alt+Enter (or {=function()} syntax) to allow the result to return a table of data (however, you'll need to know in advance the size of the output data) which is an unfortunate limitation of the old calc engine.

Including headers

Functions can optionally include headers in the results. To include headers, use the functionh keyword instead of the function keyword, for example:

{functionh myFunction(int rowsToReturn = 20)}
...

Returning results asynchronously

Functions can be declared as asynchronous. This is useful for long running functions, as async functions do not block Excel while executing. To make a function asynchronous, simply use the async keyword.

{async function myFunction(int rowsToReturn = 20)}
...

Async functions can also include headers in results:

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

Workbook-scoped functions

If the function is stored inside the workbook, anyone who has the workbook (and the QueryStorm runtime) will be able to use it. However, this function will only be usable in the workbook that contains it.

Scripts created using the buttons in the ribbon are always created inside the current workbook.

If the function should be usable in all workbooks, you should define it as a global function, as described below.

Global functions

QueryStorm supports creating extension projects which can define Excel functions that are visible in any workbook and can also be published for use by other users.

To define the function in a QueryStorm extension rather than in the workbook, create a new project in the "Code explorer" pane and then add a new script from the context menu of the new project.

For a video demonstration of this process, click below:

Publishing extension functions

Functions that are defined in extension projects can be published to other QueryStorm (Runtime) users. For more information on this, see the Publishing extension apps section.