Use parameters in SQL queries

This article explains how to use parameters when writing example SQL instructions in a Genie space.

Why use parameters?

Parameters let you write example queries with placeholders for specific values to be substituted at runtime. With parameters, Genie can take specific inputs from user questions and reuse the structure of an example query to provide verified answers as trusted assets. For example, you could adjust the previous SQL query to include a parameter that filters by the o.forecastcategory value, enabling the query to address questions about opportunities tagged in different forecast categories. For more information about how to use parameterized queries to generate responses labeled as trusted assets, see Use trusted assets in AI/BI Genie spaces.

The following example query calculates the total open pipeline value sales opportunities by region. It includes a parameter for the o.forecastcategory value. Parameters use the same syntax as named parameter markers. For more guidance on using parameters in a Genie space, see Work with query parameters and Named parameter markers.

-- Return our current pipeline at a stage by region.
-- Opportunities are only considered pipelines if they are tagged as such.
  SELECT
    a.region__c AS `Region`,
    sum(o.amount) AS `Open Pipeline`
  FROM
    sales.crm.opportunity o
    JOIN sales.crm.accounts a ON o.accountid = a.id
  WHERE
    o.forecastcategory = :forecast_category AND
    o.stagename NOT ILIKE '%closed%'
  GROUP BY ALL;

To add a parameter to a query:

1. Place your cursor where you want to place the parameter in your query.

2. Click Add parameter to insert a new parameter.

   This creates a new parameter with the default name parameter. To change the default name, replace it in the query editor. You can also add parameters by typing a colon followed by a parameter name (:parameter_name) into the editor.

Edit a query parameter

To edit a parameter, do the following:

1. Click next to the parameter name. A Parameter details dialog appears and includes the following configuration options:

   • Keyword: The keyword that represents the parameter in the query. The keyword can only be changed by directly updating the text in the query.

   • Display name: The human-readable name that Genie uses in the chat experience. When Genie generates a response using a parameterized query, it includes the display name and associated value in the response.

   • Type: Supported types include String, Date, Date and Time, and Numeric.

     • The default type is String.
     • The Numeric datatype allows you to specify between Decimal and Integer. The default numeric type is Decimal.

     Note

     If the actual input value does not match the selected parameter type, Genie treats the input value as the incorrect type, which can lead to inaccurate results.

2. Click another part of the UI to close the dialog.

Parameterized query responses

When the exact text of a parameter is used in a response, the response is marked trusted. This means that Genie identified, based on the space's context, the the user question matched the intent of an example question or query.

The Trusted label lets space users know that Genie's response is based on a vetted SQL query. They can see the generated SQL and paramter values that are used in the response.