# Dynamic Named Queries

**URL:** <https://forum.inductiveautomation.com/t/dynamic-named-queries/64657>\
**Category:** Ignition\
**Tags:** database, scripting, ignition81, perspective\
**Created:** [September 29, 2022, 2:26pm UTC](https://forum.inductiveautomation.com/t/dynamic-named-queries/64657 "2022-09-29T14:26:14Z")\
**Posts on this page:** 7\
**Page:** 1

<div class="post-metadata">

**Author:** ![CMuecke](https://avatars.discourse-cdn.com/v4/letter/c/df705f/32.png) [@CMuecke](https://forum.inductiveautomation.com/u/CMuecke)\
**Post date:** [September 29, 2022, 2:26pm UTC](https://forum.inductiveautomation.com/t/dynamic-named-queries/64657/1 "2022-09-29T14:26:14Z")

</div>

I have a table consisting of an ItemNumber and Quantity.  
For example.

1. ItemNumber 1234 and Qty 10.
2. ItemNumber 1235 and Qty 5.

This table can be edited in Perspective. I wrote a script that moves the new ItemNumber into the same table.  
For example.

1. ItemNumber 1234 and Qty 10.
2. ItemNumber 1256 and Qty 5.

I have another table that will show where these Items come from.

I know I can use the item number as the parameter for the Named Query. Can I take it further using variables in that table so it would search for both ItemNumbers in 1 trigger?

---

<div class="post-metadata">

**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:** [September 29, 2022, 3:19pm UTC](https://forum.inductiveautomation.com/t/dynamic-named-queries/64657/2 "2022-09-29T15:19:11Z")

</div>

Yes, why not ?  
Just make your named query take 2 parameters instead of one.

Or is there something I’m missing ?

---

<div class="post-metadata">

**Author:** ![nminchin](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/nminchin/32/6911_2.png) [@nminchin](https://forum.inductiveautomation.com/u/nminchin)\
**Post date:** [September 29, 2022, 3:28pm UTC](https://forum.inductiveautomation.com/t/dynamic-named-queries/64657/3 "2022-09-29T15:28:40Z")

</div>

Or if you want it dynamic, you could use a query string type param and use:

```auto
WHERE ItemNumber IN ({ItemNumberCSV})

```

where ItemNumberCSV is something like

```auto
'1234,2345,9374'

```

---

<div class="post-metadata">

**Author:** ![CMuecke](https://avatars.discourse-cdn.com/v4/letter/c/df705f/32.png) [@CMuecke](https://forum.inductiveautomation.com/u/CMuecke)\
**Post date:** [September 29, 2022, 4:05pm UTC](https://forum.inductiveautomation.com/t/dynamic-named-queries/64657/4 "2022-09-29T16:05:22Z")

</div>

How do I get the ItemNumbers from the Table to the SQL parameter?

If I am grabbing this from a table that already exists, how do I get the ItemNumbers into the parameters for the Named Query? These need to be dynamic.

Sometimes the first table might have 2 items, sometimes it may have 15.

---

<div class="post-metadata">

**Author:** ![nminchin](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/nminchin/32/6911_2.png) [@nminchin](https://forum.inductiveautomation.com/u/nminchin)\
**Post date:** [September 29, 2022, 9:33pm UTC](https://forum.inductiveautomation.com/t/dynamic-named-queries/64657/5 "2022-09-29T21:33:59Z")

</div>

You need to use a script transform on the table's data, and produce the itemNumbersCsv to then pass into the named query queryString param

Eg something like

```auto
ds = value
ret = [] 
for row in range(ds.getRowCount()):
   ret.append(ds.getValueAt(row, "ItemNumber")) 

return ','.join(ret)

```

---

<div class="post-metadata">

**Author:** ![Transistor](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/transistor/32/3380_2.png) [@Transistor](https://forum.inductiveautomation.com/u/Transistor)\
**Post date:** [September 29, 2022, 9:49pm UTC](https://forum.inductiveautomation.com/t/dynamic-named-queries/64657/6 "2022-09-29T21:49:06Z")

</div>

Nick, what's the benefit of `ds = value`? I would have tried to use `range(value.getRowCount())` directly (but I only know enough Python to get by).

---

<div class="post-metadata">

**Author:** ![nminchin](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/nminchin/32/6911_2.png) [@nminchin](https://forum.inductiveautomation.com/u/nminchin)\
**Post date:** [September 29, 2022, 9:53pm UTC](https://forum.inductiveautomation.com/t/dynamic-named-queries/64657/7 "2022-09-29T21:53:49Z")

</div>

No benefit, other than understanding 🙂 especially on here where I can't show that value is coming from the binding of the table data. But I would normally just use `value` as well in my own code