topic Re: How to overcome missing query parameters in Databricks SQL? in Data Engineering

How to overcome missing query parameters in Databricks SQL?

dimsh — Thu, 23 Dec 2021 09:57:34 GMT

Hi, there!

I'm trying to build up my first dashboard based on Dataabricks SQL. As far as I can see if you define a query parameter you can't skip it further. I'm looking for any option where I can make my parameter optional.

For instance, I have a table (start_date, end_date, country_code, unique_ids) and want by default to show the data for the last 7 days. But at the same time, I want to add two inputs where a user can specify his / her arbitrary date range.

When I don't provide any value I get a "Missing values for start_date, end_date parameters" error. So if I get a null in this case I can process it via SQL, but my query just fails.

Is there any chance to overcome this limitation?

With BR,

Dmytro

Re: How to overcome missing query parameters in Databricks SQL?

Hubert-Dudek — Thu, 23 Dec 2021 13:06:16 GMT

What about widgets?

dbutils.widgets.dropdown("start_date", default_value, values)

dbutils.widgets.dropdown("end_date", default_value, values)

Re: How to overcome missing query parameters in Databricks SQL?

dimsh — Thu, 23 Dec 2021 14:55:32 GMT

@Hubert Dudek

Thanks. But I'm using Databricks SQL (not Data Engineering) and I'm only limited with SQL syntax.

https://docs.databricks.com/sql/user/queries/query-parameters.html

Re: How to overcome missing query parameters in Databricks SQL?

Anonymous — Fri, 31 Dec 2021 16:12:34 GMT

@Dmytro Imshenetskyi - Does this last answer help?

Re: How to overcome missing query parameters in Databricks SQL?

dimsh — Tue, 04 Jan 2022 15:15:48 GMT

@Kyle Hale

Thanks. It works, but from my perspective, it looks like a workaround.

Probably it will be better to consider empty values as nulls, and then process them via COALESCE SQL function.

Re: How to overcome missing query parameters in Databricks SQL?

austin_ford — Mon, 10 Jan 2022 17:14:59 GMT

@Dmytro Imshenetskyi When you are building a DBSQL dashboard, you can set the default for parameters so users don't have to select such a parameter: https://docs.databricks.com/sql/user/queries/query-parameters.html#query-parameter-mapping-in-dashboards

Re: How to overcome missing query parameters in Databricks SQL?

Anonymous — Tue, 15 Feb 2022 16:58:22 GMT

@Dmytro Imshenetskyi - Checking on you. How have these more recent answers worked out?

Re: How to overcome missing query parameters in Databricks SQL?

Axel_Schwanke — Thu, 03 Mar 2022 09:10:26 GMT

not usable in my case

iwtLegacyAnbieterId like '%{{providerId}}%'

I simply want the option of getting all records by leaving the parameter empty

Re: How to overcome missing query parameters in Databricks SQL?

Axel_Schwanke — Thu, 03 Mar 2022 09:14:40 GMT

any solution for my problem of using a parameter in a 'like' where clause, e.g.

iwtLegacyAnbieterId like '%{{providerId}}%'

and getting ALL records by leaving the parameter empty?

Re: How to overcome missing query parameters in Databricks SQL?

Anonymous — Tue, 08 Mar 2022 21:54:30 GMT

@Axel Schwanke - Let me see if I can find someone to help you. Thank you for your patience.

Re: How to overcome missing query parameters in Databricks SQL?

DavePete_79593 — Tue, 16 Aug 2022 15:26:07 GMT

Also, the mere fact that {{}} exists in the SQL will cause Databricks console to error it. While debugging I had actually moved the parameter {{}} part to a SQL comment -- {{}} and the console errors it even though it's only in a comment so obviously the above won't work.

Re: How to overcome missing query parameters in Databricks SQL?

DavePete_79593 — Tue, 16 Aug 2022 15:35:38 GMT

I have a similar problem and none of the above proposals work. I'm trying to do an array_overlap(column_array, user_input_array). The user_input_array should be optional. So I can do something like select case when cardinality(column_array) = 0 or cardinality(user_input_array) > 0 then true else array_overlap(column_array, user_input_array) end as matched. However this won't work because the SQL UI won't allow empty input parameters.

Re: How to overcome missing query parameters in Databricks SQL?

DavePete_79593 — Tue, 16 Aug 2022 15:37:50 GMT

And I can't submit a feature request because when I select feedback I get: HTTP ERROR 403

Problem accessing /sso/ideas. Reason:

Invalid or missing CSRF token

Re: How to overcome missing query parameters in Databricks SQL?

techg — Wed, 27 Nov 2024 06:51:53 GMT

Is there any solution for the above mentioned post?

Re: How to overcome missing query parameters in Databricks SQL?

theslowturtle — Mon, 11 Aug 2025 16:24:05 GMT

Hello guys, I'm not sure if you could solve this issue but here is how I've handled it:

SELECT *
FROM my_table
WHERE (CASE WHEN LEN(:my_parameter) > 0 THEN my_column = :my_parameter ELSE my_column = my_column END)

I hope this can help!