How to pass task name as parameter in scheduled job that will be used as a schema name in query

[jeroaranda]

I want to run a parametrized sql query in a task. Query:

select * from {{client}}.catalog.table

with client value being {{task.name}}.

if client is a string parameter, it is replaced with quotes which throws an error.
if table is a dropdown list parameter, it throws: The following parameter values are invalid: [{'key': 'client', 'value': 'randomclient'}]. 

How can I solve?

[Kaniz_Fatma]

Hi @jeroaranda, 

1. When you use a string parameter in your SQL query, it’s important to ensure that the parameter value is properly quoted. If you’re directly substituting the parameter value into the query, you might encounter issues with quotes. To avoid this, you can use placeholders and bind the parameter values securely.

2. Dropdown list parameters can be tricky because they have predefined values. If you’re getting an error like “The following parameter values are invalid,” it means that the value you’re trying to set for the client parameter doesn’t match any of the valid options.

    

   To resolve this issue:

   • Double-check the available options for the client parameter. Make sure you’re using a valid value.
   • If the value is dynamically generated (e.g., based on another parameter), ensure that the logic for populating the dropdown list is correct.

   • If you’re using both client and table parameters, make sure you’re handling them correctly in your Databricks task. You’ll need to:

     • Retrieve the selected client value (e.g., from a dropdown or user input).
     • Construct the SQL query dynamically by substituting the client value into the query template.

If you encounter any specific errors or need further assistance, feel free to ask! 😊