# Named Query Error in Ignition but not in SQL

**URL:** <https://forum.inductiveautomation.com/t/named-query-error-in-ignition-but-not-in-sql/70964>\
**Category:** Ignition\
**Tags:** perspective, ignition81, namedqueries, scripting, database\
**Created:** [March 13, 2023, 1:31pm UTC](https://forum.inductiveautomation.com/t/named-query-error-in-ignition-but-not-in-sql/70964 "2023-03-13T13:31:08Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![MC2338](https://avatars.discourse-cdn.com/v4/letter/m/8c91f0/32.png) [@MC2338](https://forum.inductiveautomation.com/u/MC2338)\
**Post date:** [March 13, 2023, 1:31pm UTC](https://forum.inductiveautomation.com/t/named-query-error-in-ignition-but-not-in-sql/70964/1 "2023-03-13T13:31:08Z")

</div>

I have the following query save as a named query in Ignition, and during a gateway event it is triggered in a script and is meant to flip a bit in a SQL table.

> with CTE as  
> (SELECT top (3) BagProcessed  
> FROM [PLC].[dbo].[Production\_TBL\_Test]  
> WHERE Dest\_Type like '%Bag%' and BagProcessed not in (1) and Roaster\_ID = 2  
> order by RoastDate asc, RoastTime asc)  
> UPDATE CTE  
> SET  
> CTE.[BagProcessed] = 1

When I run this query in SQL, it works as desired. When I try to run it in the Named Query test tab, I get the error "Index 2 out of range"

Please help.

---

<div class="post-metadata">

**Author:** ![dkhayes117](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/dkhayes117/32/68344_2.png) [@dkhayes117](https://forum.inductiveautomation.com/u/dkhayes117)\
**Post date:** [March 13, 2023, 1:37pm UTC](https://forum.inductiveautomation.com/t/named-query-error-in-ignition-but-not-in-sql/70964/2 "2023-03-13T13:37:27Z")

</div>

This is a limitation of the JDBC driver which can only have a single _statement_ (I think that is the right term). In your query, you have a select and an update statement which would have to be separated in Ignition. You could make this a stored procedure in your db and call it from Ignition as an alternative.

---

<div class="post-metadata">

**Author:** ![MC2338](https://avatars.discourse-cdn.com/v4/letter/m/8c91f0/32.png) [@MC2338](https://forum.inductiveautomation.com/u/MC2338)\
**Post date:** [March 16, 2023, 2:50pm UTC](https://forum.inductiveautomation.com/t/named-query-error-in-ignition-but-not-in-sql/70964/3 "2023-03-16T14:50:48Z")

</div>

So I made this query a stored procedure in SQL, and my NamedQuery calls it but I am still getting the same error. Any suggestions?

---

<div class="post-metadata">

**Author:** ![dkhayes117](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/dkhayes117/32/68344_2.png) [@dkhayes117](https://forum.inductiveautomation.com/u/dkhayes117)\
**Post date:** [March 16, 2023, 3:09pm UTC](https://forum.inductiveautomation.com/t/named-query-error-in-ignition-but-not-in-sql/70964/4 "2023-03-16T15:09:40Z")

</div>

You have to create a stored procedure call if you go this route.  
[system.db.createSProcCall - Ignition User Manual 8.1 - Ignition Documentation (inductiveautomation.com)](https://docs.inductiveautomation.com/display/DOC81/system.db.createSProcCall)