Parameterized Queries

An introduction to parameterized queries and query templates.

Another piece of data.world-specific functionality is to declare parameters for a query. Queries with parameters allow you to easily vary the results of 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 begining 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 and optional default value. The default value must be a literal value, either STRING, NUMERIC, BOOLEAN, DATE, or TIME. Once you’ve declared a parameter, then 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
account order_total
Rundofase 110512

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
account order_total
Rundofase 110512
Parameters with default values may be used as a way to separate out important constant values from the rest of your query structure, for easy modification.

The real value of parameterized queries isn’t just in helping to organize your the constant values in your query. The real value comes from being able to override the default value of the param, without modifying your query. 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.