# DISCUSSION: Dynamic WHERE clauses on named queries **URL:** \ **Category:** Ignition\ **Tags:** ignition81\ **Created:** [March 25, 2025, 1:22pm UTC](https://forum.inductiveautomation.com/t/discussion-dynamic-where-clauses-on-named-queries/104154 "2025-03-25T13:22:20Z")\ **Posts on this page:** 9\ **Page:** 1
**Author:** ![danielps1818](https://avatars.discourse-cdn.com/v4/letter/d/ecae2f/32.png) [@danielps1818](https://forum.inductiveautomation.com/u/danielps1818)\ **Post date:** [March 25, 2025, 1:22pm UTC](https://forum.inductiveautomation.com/t/discussion-dynamic-where-clauses-on-named-queries/104154/1 "2025-03-25T13:22:20Z")
Let me give you a bit of context. In our current project we have about 10-30 queries with different where clauses. Something like this: ```SQL SELECT ORDERNUM FROM TABLE WHERE ID = :ID ``` ```SQL SELECT ORDERNUM FROM TABLE WHERE TYPE = :TYPE AND ITERATION = :ITERATION ``` ```SQL SELECT ID, ORDERNUM FROM TABLE WHERE PRODUCT = :PRODUCT ``` `...` One of my coworker suggested that we could use something like this to only have 1 named query and to be able to reuse them instead of creating new named queries. ```SQL SELECT * FROM TABLE WHERE {WHERECLAUSE} ``` You would pass by parameter the specific conditions that you need. However, I'm a bit worried of how dinamic could it be and the problems that this could generate. In our team we have our opinion on what are we going to do, but we thought that asking here was a good idea. **What is your opinion about this?** _Note: Normally the queries we need/use don't have user interation so we don't have problem of security (at least at first glance)_ ---
**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\ **Post date:** [March 25, 2025, 1:46pm UTC](https://forum.inductiveautomation.com/t/discussion-dynamic-where-clauses-on-named-queries/104154/2 "2025-03-25T13:46:38Z")
You **can** do this, but you cannot reliably pass any string values in that kind of where clause. If any part of that Where clause comes directly from user input (typical for this use), then you really do have a security problem, too. This is one of the cases where a scripted query is better, as you can construct a dynamic WHERE clause _structurally_, but still use `?` substitution parameters to safely pass user input to the DB. ---
**Author:** ![pascal.fragnoud](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pascal.fragnoud/32/24728_2.png) [@pascal.fragnoud](https://forum.inductiveautomation.com/u/pascal.fragnoud)\ **Post date:** [March 25, 2025, 6:39pm UTC](https://forum.inductiveautomation.com/t/discussion-dynamic-where-clauses-on-named-queries/104154/3 "2025-03-25T18:39:31Z")
My rule of thumb is: If I need to pass anything else than simple parameters to a named query, then it shouldn't be a named query. ---
**Author:** ![Lynn\_Martineau](https://avatars.discourse-cdn.com/v4/letter/l/f1d935/32.png) [@Lynn\_Martineau](https://forum.inductiveautomation.com/u/Lynn_Martineau)\ **Post date:** [March 25, 2025, 9:57pm UTC](https://forum.inductiveautomation.com/t/discussion-dynamic-where-clauses-on-named-queries/104154/4 "2025-03-25T21:57:21Z")
I use a compound WHERE clause and pass 'ALL' for string fields or a 'magic number' for numeric fields. Here's an example: ```python WHERE (t.PartNumber = :partNumber OR :partNumber = 'ALL') AND (t.LotNumber = :lotNumber OR :lotNumber = 'ALL') AND (t.BuildNumber = :buildNumber OR :buildNumber = 'ALL') AND (t.ScaleID = :scaleId OR :scaleId = 'ALL') AND (t.MixID = :mixId OR :mixId = 0 ) ``` The user effectively filters the results to see what they want without needing to write multiple queries or use scripted queries. In this example, the user can see everything for a part number or everything for a building, or everything for a specific part number on a specific scale, etc. I typically use this when there are drop-downs with 'ALL' as the first option. But... everything has a cost, complex WHERE clauses _can_ become a performance hit on very large datasets and especially beware of date ranges which easily cause slowness without proper indexes or constrained min/max dates. ---
**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\ **Post date:** [March 25, 2025, 10:02pm UTC](https://forum.inductiveautomation.com/t/discussion-dynamic-where-clauses-on-named-queries/104154/5 "2025-03-25T22:02:48Z")
> [@Lynn\_Martineau](#): > > But... everything has a cost Exactly. Casual use of compound where clauses with that sort of wildcard option are time bombs. Particularly if handed to less experienced designers. Just say no. ---
**Author:** ![Lynn\_Martineau](https://avatars.discourse-cdn.com/v4/letter/l/f1d935/32.png) [@Lynn\_Martineau](https://forum.inductiveautomation.com/u/Lynn_Martineau)\ **Post date:** [March 25, 2025, 10:11pm UTC](https://forum.inductiveautomation.com/t/discussion-dynamic-where-clauses-on-named-queries/104154/6 "2025-03-25T22:11:54Z")
> [@pturmel](#): > > that sort of wildcard option `:parameter = 'literal'` is true for every row in the table and takes 0 execution plan/explain plan time. The caution is for having too many fields in the WHERE clause. ---
**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\ **Post date:** [March 25, 2025, 10:19pm UTC](https://forum.inductiveautomation.com/t/discussion-dynamic-where-clauses-on-named-queries/104154/7 "2025-03-25T22:19:13Z")
> [@Lynn\_Martineau](#): > > takes 0 execution plan/explain plan time. It's presence prevents full optimization of the execution plan, because the DB won't know if that shortcut applies until it receives the values to execute. Modern databases make their execution plan when the statement is prepared, not when actual parameters are submitted. ---
**Author:** ![danielps1818](https://avatars.discourse-cdn.com/v4/letter/d/ecae2f/32.png) [@danielps1818](https://forum.inductiveautomation.com/u/danielps1818)\ **Post date:** [March 27, 2025, 9:01am UTC](https://forum.inductiveautomation.com/t/discussion-dynamic-where-clauses-on-named-queries/104154/8 "2025-03-27T09:01:29Z")
Yeah, but if you have 2 or 3 different part of the code that could reuse the same NamedQuery what would you do? If you don't create a NamedQuery and then change the db, you have to go to all of those spots to change manually the queries. ---
**Author:** ![pascal.fragnoud](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pascal.fragnoud/32/24728_2.png) [@pascal.fragnoud](https://forum.inductiveautomation.com/u/pascal.fragnoud)\ **Post date:** [March 27, 2025, 9:02am UTC](https://forum.inductiveautomation.com/t/discussion-dynamic-where-clauses-on-named-queries/104154/9 "2025-03-27T09:02:13Z")
You make a function that holds the query and you call that function