How to add a select all option in a Databricks SQL parameter? I would like to use a query-based drop-down list.

[StephanieRivera]

So I want to create a select all button in a parameter. The actual parameter has around 200 options because of the size of the database. However, if I want a general summary where you can see all the options I would have to select one by one and that is not efficient. Any ideas?

Select
*
from Table
where store in ({{store}}) or ({{store}}) is null

I tried this but it didn´t let me use the parameter as null. Also, it is important to mention that the parameter uses values from another query where the distinct names of the stores are listed.

As shown here: https://stackoverflow.com/questions/71622404/how-to-add-a-select-all-option-in-a-sql-databricks-para...

[StephanieRivera · Accepted solution]

You could add '--- All Stores ---' to your list. Here is the query I would use to populate the drop-down. S.O. answer here

SELECT
    store as store_name
FROM
    (
    Select Distinct
      store
    From 
      Table
 
    UNION ALL
 
    SELECT
      '--- All Stores ---' AS store
    )
ORDER BY
  store_name 

Next the query that uses the drop down

Select
  *
FROM 
  Table
WHERE 
  (store in ({{store_name}}) or '{{store_name}}' LIKE '--- All Stores ---')

 Now if you select '--- All Stores ---' you should get all of the stores 🙂

[susodapop]

This is the right approach. You can avoid mixing the `IN` and `LIKE` syntax in this way for your second query:

SELECT * FROM table WHERE
('--- All Stores ---'in {{ PARAM }} or store_name in {{ PARAM }})

Then configure `PARAM` to be a multi-select parameter.

I would also reorder your queries in the backing parameter query so that `--- All Stores ---` always appears at the top of the list, rather than the bottom.