PopSQL

Dynamic SQL-Powered Query Variables

February 15th, 2023
Post Cover Image

You can now create query variables that display a list of options from your database. The list is powered by a SQL query that returns a list of values and labels. For example, a list of Customer IDs as the value populated in your main query for a faster `WHERE` statement, and customer names as labels to make it clear what is being selected. Dynamic SQL variables work as single-select or a multi-select drop-downs.

These variables are great for filtering on longer lists in your database so you don't have to manually maintain the list of options, e.g. Cities, Customers, Users, Products. The most common use case we've seen so far is to return a list of customers. You can return the customer's name in the label field and the ID in the value field. That way your end users viewing the query or dashboard will see customer names so the list of options is easily searchable, but the IDs will be rendered into the SQL query for better performance filtering your results.

The list of values is limited at 100 to ensure consistent performance. If you need more values you can add a search token to your query. You will just need to set up the query with a search_token. You can find more details about how to set them up on our query variable documentation page.

Ready for a modern SQL editor?