Use named parameter markers

Named parameter markers let you insert variable values into SQL queries at runtime. Instead of hard-coding specific values, you define typed placeholders that users fill in when the query runs. This improves query reuse, prevents SQL injection, and makes it easier to build flexible, interactive queries.

Named parameter markers work in the following Databricks surfaces:

• SQL editor (new and legacy)
• Notebooks
• AI/BI dashboard dataset editor
• Genie spaces

Add a named parameter marker

Insert a parameter by typing a colon followed by a parameter name, such as :parameter_name. When you add a named parameter marker to a query, a widget appears where you can set the parameter type and value. See Work with parameter widgets.

This example converts a hard-coded query to use a named parameter.

Starting query:

SELECT
  trip_distance,
  fare_amount
FROM
  samples.nyctaxi.trips
WHERE
  fare_amount < 5

1. Delete 5 from the WHERE clause.
2. Type :fare_parameter in its place. The last line should read fare_amount < :fare_parameter.
3. Click the gear icon near the parameter widget.
4. Set the Type to Decimal.
5. Enter a value in the parameter widget and click Apply changes.
6. Click Save.

Parameter types

Set the parameter type in the parameter settings panel. The type determines how Databricks interprets and handles the value at runtime.

Type	Description
String	Free-form text. Backslash, single, and double quotation marks are escaped automatically. Databricks adds quotation marks around the value.
Integer	Whole number value.
Decimal	Numeric value that supports fractional values.
Date	Date value. Uses a calendar picker and defaults to the current date.
Timestamp	Date and time value. Uses a calendar picker and defaults to the current date and time.

Named parameter syntax examples

The following examples show common patterns for named parameter markers.

Insert a date

SELECT
  o_orderdate AS Date,
  o_orderpriority AS Priority,
  sum(o_totalprice) AS `Total Price`
FROM
  samples.tpch.orders
WHERE
  o_orderdate > :date_param
GROUP BY 1, 2

Insert a number

SELECT
  o_orderdate AS Date,
  o_orderpriority AS Priority,
  o_totalprice AS Price
FROM
  samples.tpch.orders
WHERE
  o_totalprice > :num_param

Insert a field name

Use the IDENTIFIER function to pass a column name as a parameter. The parameter value should be a column name from the table used in the query.

SELECT * FROM samples.tpch.orders
WHERE IDENTIFIER(:field_param) < 10000

Insert database objects

Use the IDENTIFIER function with multiple parameters to specify a catalog, schema, and table at runtime.

SELECT *
FROM IDENTIFIER(:catalog || '.' || :schema || '.' || :table)

See IDENTIFIER clause.

Concatenate multiple parameters

Use format_string to combine parameters into a single formatted string. See format_string function.

SELECT o_orderkey, o_clerk
FROM samples.tpch.orders
WHERE o_clerk LIKE format_string('%s%s', :title, :emp_number)

Work with JSON strings

Use the from_json function to extract a value from a JSON string using a parameter as the key. Substituting a as the value for :param returns 1.

SELECT from_json('{"a": 1}', 'map<string, int>') [:param]

Create an interval

Use CAST to convert a parameter value to an INTERVAL type for time-based calculations. See Interval type.

SELECT CAST(:param AS INTERVAL MINUTE)

Add a date range using .min and .max

Date and Timestamp parameters support a range widget. Use .min and .max to access the start and end of the range.

SELECT * FROM samples.nyctaxi.trips
WHERE tpep_pickup_datetime
BETWEEN :date_range.min AND :date_range.max

Set the parameter type to Date or Timestamp and the widget type to Range.

Add a date range using two parameters

SELECT * FROM samples.nyctaxi.trips
WHERE tpep_pickup_datetime
BETWEEN CAST(:date_range_min AS TIMESTAMP) AND CAST(:date_range_max AS TIMESTAMP)

Parameterize rollup granularity

Use DATE_TRUNC to aggregate results at a user-selected level of granularity. Pass DAY, MONTH, or YEAR as the parameter value.

SELECT
  DATE_TRUNC(:date_granularity, tpep_pickup_datetime) AS date_rollup,
  COUNT(*) AS total_trips
FROM samples.nyctaxi.trips
GROUP BY date_rollup

Pass multiple values as a string

Use ARRAY_CONTAINS, SPLIT, and TRANSFORM to filter on a comma-separated list of values passed as a single string parameter. SPLIT parses the comma-separated string into an array. TRANSFORM trims whitespace from each element. ARRAY_CONTAINS checks whether the table value appears in the resulting array.

SELECT * FROM samples.nyctaxi.trips WHERE
  array_contains(
    TRANSFORM(SPLIT(:list_parameter, ','), s -> TRIM(s)),
    CAST(dropoff_zip AS STRING)
  )

Note

This example works for string values. To use other data types, wrap the TRANSFORM operation with a CAST to convert the elements to the desired type.

Syntax migration reference

Use this table when converting queries from mustache syntax to named parameter markers. See Mustache parameter syntax for more information about the legacy syntax.

Use case	Mustache syntax	Named parameter syntax
Filter by date	WHERE date_field < '{{date_param}}'	WHERE date_field < :date_param
Filter by number	WHERE price < {{max_price}}	WHERE price < :max_price
Compare strings	WHERE region = '{{region_param}}'	WHERE region = :region_param
Specify a table	SELECT * FROM {{table_name}}	SELECT * FROM IDENTIFIER(:table) — use the full three-level namespace
Specify catalog, schema, and table	SELECT * FROM {{catalog}}.{{schema}}.{{table}}	SELECT * FROM IDENTIFIER(:catalog \|\| '.' \|\| :schema \|\| '.' \|\| :table)
Format a string from multiple parameters	"({{area_code}}) {{phone_number}}"	format_string("(%d) %d", :area_code, :phone_number)
Create an interval	SELECT INTERVAL {{p}} MINUTE	SELECT CAST(format_string("INTERVAL '%s' MINUTE", :param) AS INTERVAL MINUTE)