SQL Parameterization in Databricks SQL

Databricks SQL Warehouse supports robust parameterization, which you must know before starting with SQL scripting, which was introduced in runtime 16.3.
Databricks Runtime 16.1 enhanced parameterization capabilities by allowing IDENTIFIER usage in the USE CATALOG statement.
This article uses a structured approach to explore variables, parameter markers, and EXECUTE IMMEDIATE.

If you are not yet a member of Medium, you can access for free via a friend’s link

Variables in Databricks SQL

Variables store temporary values that persist within a session. They can be directly assigned or dynamically set with a query.

DECLARE OR REPLACE VARIABLE sal_range INT;
SET VAR sal_range = 20000;

SELECT * FROM employees
WHERE sal > sal_range;

Parameter Markers (Widgets in SQL Editor)

Databricks SQL Editor and notebooks support widgets (UI parameters) for dynamic user input. You can also pass them through jobs. These are referenced with :param_name syntax inside queries.

SELECT * FROM employees
WHERE sal > :sal_range;

Using IDENTIFIER for Dynamic Objects

IDENTIFIER() allows dynamic substitution of object names (catalog, schema, table) to prevent SQL injection.

DECLARE OR REPLACE VARIABLE table_name STRING = 'employees';

SELECT * FROM IDENTIFIER(table_name);

Dynamic SQL with EXECUTE IMMEDIATE

EXECUTE IMMEDIATE executes a dynamically constructed SQL string at runtime.

DECLARE OR REPLACE sqlStr STRING = 'SELECT * FROM employees WHERE sal > ?';

EXECUTE IMMEDIATE sqlStr USING 2000;

Parameterizing USE CATALOG (Databricks 16.1 Update)

As of Runtime 16.1, USE CATALOG supports IDENTIFIER, enabling flexible catalog selection.

DECLARE OR REPLACE VARIABLE catalog_name STRING = 'workspace';

USE CATALOG IDENTIFIER(catalog_name);

Combining Variables and parameters with EXECUTE IMMEDIATE