Apps (SQL)

Users can use SQL for more than just running queries - they can also build apps. Two kinds of functionality can be created with SQL apps:

• Defining custom Excel functions that can be used on the current machine or shared with other users.
• Setting up automated workbooks that load and save database data as the user interacts with the workbook.

Both kinds of functionality are defined with SQL scripts using a simple preprocessor syntax that allows declaring functions and event handlers.

Custom functions

Defining functions in SQL is useful for returning data from databases.

The function body is written in SQL, while the function declaration is done using the preprocessor.

For example:

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

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

Functions can be defined in a particular workbook or globally. Workbook functions are contained and distributed as part of the workbook while globally defined functions can be published as separate packages that end users can download through the Extension Manager that's part of the QueryStorm runtime.

To read more about creating functions with SQL, click here.

Workbook automation

QueryStorm also allows setting up automation so that SQL queries and commands are triggered by workbook events (e.g. button clicked, cell's value changed).

Automation is set up using scripts that contain SQL code extended with a simple preprocessor syntax.

{handler('Sheet1!btnSaveToDb')}

{@results_table}
select 
    * 
from
    HumanResources.Departments
where 
    group = @selectedGroup

The job of the SQL code is to interact with the database, while the job of the preprocessor code is to interact with Excel, specifically, to send query results into the workbook and to specify which events will trigger the execution of the script.

For more information about automation via SQL, click here.