Skip to main content

Creating SQL query templates

The steps in this section are explained using a sample dataset and query. This example only uses one variable but you can extend your query to use multiple variables making your query templates more roburst.

Steps include: Create a query, declare a variable, use the variable in the query, add comments to give instructions on using the template, and save the template.

To create a query template:

  1. Create a new SQL query.

    Screen_Shot_2019-07-18_at_3.38.20_PM.png

  2. Click the New template button in the query editor. It opens the Create a query template window with sample code. Click the Add a sample statement button to add the sample code in the query.

    window_create_query_template.png

  3. Modify the variable for use in the query or create your own variable. In this example, year is the variable name, INTEGER is the data type, and 2018 is the optional default value. If no default value is specified and the query is run without entering a value manually, the query will return an error. Allowed data types are: STRING, INTEGER, DECIMAL, BOOLEAN, DATE, TIME, and DATETIME.

    query-template-03.png

  4. Use the variable in the query. Choose where the variable is to be used in the query. This will often be in the WHERE clause of the query , although it can also be used in ORDER BY, LIMIT, or other clauses. In our example, we'll use it in the WHERE clause.

    Note

    This example is a bit complicated because the survey_year has the data type YEAR in the database, but we cannot declare a variable with that type. Instead, we declare our variable as a INTEGER and then used the CAST function to convert the value of survey_year from YEAR to INTEGER to make the comparison on Line 5.

    query-template-04.png

  5. Add comments above the Declare statement to provide instructions and present valid input values. Denote comments by adding the # or -- characters before the text. Comments added before the DECLARE statement display in the final query template form.

    query-template-05.png

  6. Click the Preview query template button to view and use your query template.

    query-template-06.png

  7. Click Save to save the query template in a project.

  8. Any time you want to make changes to the query or the template, click the Edit query link.

In this section: