How to use Widgets with SQL Endpoint in Databricks?

[Anmol_Chauhan]

I' trying to use widgets with SQL Endpoints but I'm encountering an error, whereas they work seamlessly with Databricks Interactive Cluster. While query parameters can substitute widgets in SQL endpoints, but I specifically require dropdown and multifilter functionality, which widgets provide.

What I want to achieve:

1. Use SQL endpoint with Widgets functionality (dropdown and multiselect).

2. How to choose all values of choices by default in multiselect widget.

Can anyone assist me in finding a solution?

[Walter_C]

Widgets are primarily designed to work within Databricks notebooks and dashboards. They are not directly supported in SQL Endpoints. However, you can use query parameters as an alternative in SQL Endpoints. This means you can pass parameters to your SQL queries, but you won't have the interactive dropdown and multiselect functionality that widgets provide in notebooks.

If you specify your parameter as '{{parameter_name}'  in the SQL editor it will give you more options to customize the widget

[Anmol_Chauhan]

@Walter_C Thanks for previous solution, Can you please help me with this also:

How to choose all values of choices by default in multiselect widget?

For ex:

Below query I used to create multiselect widget with name test, I have default value as "val1",
`create widget multiselect test default "val1" choices select * from (values ("val1"), ("val2"), ("val3"))`

but I want all values to be selected/checked by default:

for that I tried "*" and "" and "All" as default value but nothing worked:
`create widget multiselect test default "*" choices select * from (values ("val1"), ("val2"), ("val3"))`
`create widget multiselect test default "" choices select * from (values ("val1"), ("val2"), ("val3"))`
`create widget multiselect test default "All" choices select * from (values ("val1"), ("val2"), ("val3"))`

So, what I have to choose as default value, to select/checked all values by default as we have 150 values, and it is not possible to select 150 values from multiselect dropdown one by one?

[Anmol_Chauhan]

.

[Walter_C]

Got it, let me test, i think there is no specific way to do it, but if you add the option All as the first on the list it should select it