Parameterized Queries

Another piece of data.world-specific functionality is the ability to declare parameters for a query. Queries with parameters allow you to easily vary the results based on input values without changing the content of the underlying query.

Queries which have parameter values declared are often referred to as parameterized queries. In the data.world user interface, you’ll also see them referred to as query templates.

Parameters are defined at the beginning of a query using a declare statement. Declare statements start with the keyword DECLARE, followed by the name of the parameter (starting with a question mark) followed by the type of the parameter and an optional default value. The default value must be a literal value, either STRING, NUMERIC, BOOLEAN, DATE, or TIME. Once you’ve declared a parameter, you can then use that parameter in your query, just as though it were any other value.

Queries may declare multiple parameters. If a parameter is declared without a default value, a value must be provided at run-time. If a parameter is declared without a type, the type is assumed to be STRING.

In the CRM dataset we have been using, we have a table called central_office_orders which lists every order taken by the central office. A common thing to do with such data is to ask for the total orders placed by a given account. For example, we might want to know the total orders for account "Rundofase". Here’s how we would do that with a non-parameterized query.

SELECT account, SUM(order_total)
  FROM central_office_orders
 WHERE account = "Rundofase"
 GROUP BY account

If we add a parameter for the account name we wish to know about, the query looks like this:

DECLARE ?account AS STRING = "Rundofase"

SELECT account, SUM(order_total)
  FROM central_office_orders
 WHERE account = ?account
 GROUP BY account

The real value of parameterized queries isn’t just in helping to organize the constant values in your query. The real value comes from being able to override the default value of the parameter without modifying your query. See our blog post Expand your data’s reach using parameterized queries to find out more. The data.world workspace UI provides special functionality for working with query templates. If you type a parameterized query into the workspace query interface, you’ll see a button labeled “Preview query template”. This interface shows you a customized form for setting parameter values and running the query.