Using DBeaver Query Variables to Build Reusable SQL Statements

The problem: constantly editing filter values

When you repeatedly run ad‑hoc reports or tweak a query for different date ranges, you often find yourself hunting through the SQL editor to replace literal values. Each manual change not only wastes time but also raises the chance of a typo that produces wrong results or a syntax error.

Thesis: DBeaver’s built‑in query variable substitution streamlines reuse

DBeaver (version 7.0 or newer) lets you define placeholders in any SQL script using the ${variable_name} syntax. At execution time the IDE pops up a dialog, you supply the values, and the statement runs with those values substituted. The same script can be re‑run with new inputs without touching the SQL text.

How to set up a parameterized query

1. Open DBeaver and create a new SQL script (File → New → SQL Script).
2. Enter a query that uses variables, for example:

SELECT
    order_id,
    customer_name,
    order_date,
    total_amount
FROM sales.orders
WHERE region = '${region}'
  AND order_date >= '${start_date}'
  AND order_date <  DATE_ADD('${start_date}', INTERVAL 1 MONTH);

Notice the ${region} and ${start_date} placeholders. No special delimiters are required beyond the curly braces and dollar sign.

1. Press Ctrl+Enter (or click the Execute button). DBeaver intercepts the script, analyzes it for variable patterns, and displays a dialog titled “Enter variable values”.
2. Fill in the fields – e.g., region = 'West' and start_date = '2024-01-01' – and click OK.
3. The query runs, and the result set appears in the Data tab. If you change the values in the prompt and re‑execute, the result set updates instantly.

Worked example: a monthly sales‑by‑region report

Suppose you need a report that shows total sales per region for any month you choose. The variable‑driven query looks like this:

SELECT
    region,
    SUM(total_amount) AS monthly_total
FROM sales.orders
WHERE order_date >= '${start_date}'
  AND order_date <  DATE_ADD('${start_date}', INTERVAL 1 MONTH)
GROUP BY region
ORDER BY monthly_total DESC;

Running the script prompts for start_date. Entering '2024-03-01' returns March 2024 totals; changing the prompt to '2024-04-01' refreshes the view for April without editing the SQL.

Trade‑off: where variables work and where they don’t

• Supported contexts: SQL editor windows, data‑set scripts, and the SQL console when you execute a script manually.
• Unsupported contexts: Stored‑procedure editors, the SQL editor when you run a routine via CALL, and any execution triggered from the command‑line interface (dbeaver-cli) or headless automation.
• Because the substitution happens inside the IDE’s execution layer, external tools that bypass DBeaver’s UI will see the raw ${…} text and typically raise a syntax error.

If you need to automate the same parameterized query outside DBeaver, consider exporting the SQL to a file and using a wrapper script that replaces the placeholders before sending the text to your database client.

Practical verification steps

1. Confirm you are on DBeaver 7.0 or later (Help → About).
2. Create a new SQL script, paste the sample query with variables, and execute it.
3. Verify that a dialog appears asking for each variable name.
4. Enter test values, run the query, and inspect the result set.
5. Change one value in the dialog, re‑run, and ensure the result set reflects the new input.
6. Open Help → Search and look for “query variables” to see the version‑specific documentation.

Actionable closing

Start by converting one of your most‑frequently edited ad‑hoc queries into a variable‑driven script. The immediate benefit is fewer manual edits and a lower risk of typos. As you grow comfortable, build a small library of parameterized snippets for common reporting patterns—date ranges, region filters, status codes—and reuse them across projects. Remember the limitation: the feature shines inside DBeaver’s UI but does not extend to stored‑procedure editing or headless CLI runs, so plan accordingly for automation pipelines.