How to Use Parameters in SQL

You can use the Parameters in SQL queries so that you can dynamically generate SQL queries without modifying the queries themselves.

Here's a sales data that is imported from a MySQL database.

Clicking on the token inside the Source step to open the SQL editor.

We have this very simple query.

Now we want to parameterize this SQL query so that we can set the Order Date condition value and the Market values dynamically without directly modifying the query itself every time we want to.

Create Parameters

You can click on the Parameter button to open the Parameter pane.

At this point, there is no parameter. Click 'Setup' button at the top to open Parameters dialog.

For this example, we want to create two parameters. One is a Date data type parameter that will be used for the Order Date condition. Another is a Character data type parameter that will be used to set the Market values.

Character Parameter

Now, it's time to create 'Market' parameter.

Again, click on the 'Create New Parameter' button and type in 'Market' for the Name, and select 'Character' data type.

We want to make this parameter as a dropdown parameter and allow to select multiple values. So, select 'List of Values (Multi Select)' for the Input Type and 'Dropdown' for the Style.

Return All Values When No Value Is Selected

Here is an important thing.

If I don't select any values in the dropdown parameter I want the SQL query to consider that all values are selected. Meaning, I want the data for all the Markets to be returned.

For this reason, we want to make sure to check 'Set all values when no value is selected.' checkbox and select 'Return All Values'. This will return all the Market values when I don't select any values in the parameter dropdown.

Create List of Values

If you have already created a data frame with a SQL query to list all the Market values then you can select 'Get Values from Data Frame' and select the data frame.

But for this example, we'll simply type in the possible Market values.

You can set the default value for this parameter, but you don't need to. Remember, we have set 'Set all values when no value is selected' option? That means, not setting the default value will make this parameter to return all the values for the SQL query. And that means, it'll return the data for all the Markets.

Once it looks good, Click on the Save button.

Now it's time to use these parameters!

Embed Parameters inside SQL

Go back to the SQL editor and replace the original date value with the 'Order_Date' parameter.

You can type '@' which will return a list of the parameters you can choose from.

Similarly, you can replace the original Market value with the 'Market' parameter.

Type '@', which will show you the parameter dropdown, select 'Market' parameter.

Make sure that the parameter is inside the brackets.

Run Queries with Parameters

Now, it's time to test them out.

Click on the Refresh icon at the top of the Parameter pane.

This will parse the SQL query, find the parameters that are needed to run the query, and show the parameters.

Now you can select the values for both Order Date and Market parameters and click the Run button. This will replace the parameter quotes inside the query with the selected values, then run the updated SQL query to get the data from MySQL database.

When no value is selected

Do you remember that we have setup the Market parameter so that it will return all the values when no value is selected?

Here, I'm not selecting any value from the dropdown, and running the query.

It returns all the Markets.

You can run SQL with Parameter in Summary / Chart View, etc.

Once you setup the SQL query with the Parameters, most likely you won't open the SQL editor to change the parameter values and run the query.

Instead, you can open the Parameter pane directly from one of the views such as Summary, Table, Chart, or Analytics, and change the parameter values. This will run the updated query, import the data, and update the view you are seeing dynamically.

Click on the Parameter button at the right hand side top to open the Parameter pane.

And select values and click the 'Run' button.

You can check the updated data.

Run a Dynamic SQL Query with Parameter at Server

More often than not, you want to share this parameterized data with others so that they can change the parameter values, run the query, and get the corresponding data without writing SQL queries.

For this, you can publish the data to Exploratory Server.

Once it's published, then open the published data in the web browser.

In order to use the Parameter, you can turn on the 'Interactive mode' option.

Then, the rest is the same even in the Server!