# SQL Error about Subquery when exec stored procedure

**URL:** <https://forum.inductiveautomation.com/t/sql-error-about-subquery-when-exec-stored-procedure/102092>\
**Category:** Ignition\
**Tags:** database, scripting, perspective\
**Created:** [February 10, 2025, 8:54am UTC](https://forum.inductiveautomation.com/t/sql-error-about-subquery-when-exec-stored-procedure/102092 "2025-02-10T08:54:38Z")\
**Posts on this page:** 9\
**Page:** 1

<div class="post-metadata">

**Author:** ![Raphael](https://avatars.discourse-cdn.com/v4/letter/r/edb3f5/32.png) [@Raphael](https://forum.inductiveautomation.com/u/Raphael)\
**Post date:** [February 10, 2025, 8:54am UTC](https://forum.inductiveautomation.com/t/sql-error-about-subquery-when-exec-stored-procedure/102092/1 "2025-02-10T08:54:38Z")

</div>

Hello,

I have a button on perspective who fire a named query.  
The named query execute a stored procedure.  
The stored procedure consist of an update and an insert in a log table.

It works but I have the following warning in the gateway log.

```python
Error running action 'component.onActionPerformed' on partials/DetailsRun@C$0:0/root/RunPresent/Buttons/BtnFinDeRun: Traceback (most recent call last): File "<function:runAction>", line 2, in runAction at com.inductiveautomation.ignition.gateway.db.namedquery.NamedQueryExecutor$NamedQueryInstance.execute(NamedQueryExecutor.java:407) at com.inductiveautomation.ignition.gateway.db.namedquery.NamedQueryExecutor.execute(NamedQueryExecutor.java:173) at com.inductiveautomation.ignition.gateway.db.namedquery.GatewayNamedQueryManager.execute(GatewayNamedQueryManager.java:92) at com.inductiveautomation.ignition.common.db.namedquery.SecuredNamedQueryManager.execute(SecuredNamedQueryManager.java:78) at com.inductiveautomation.ignition.gateway.script.GatewayDBUtilities.runNamedQuery(GatewayDBUtilities.java:418) at jdk.internal.reflect.GeneratedMethodAccessor44.invoke(Unknown Source) at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source) at java.base/java.lang.reflect.Method.invoke(Unknown Source) java.lang.Exception: java.lang.Exception: java.sql.SQLException: La sous-requête a retourné plusieurs valeurs. Cela n'est pas autorisé quand la sous-requête suit =, !=, <, <= , >, >= ou quand elle est utilisée en tant qu'expression.

```

If I execute the same stored procedure in SQL Management Studio I don't get any error.

I don't understand the problem. Anyone as an idea ?

Thank you

---

<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:** [February 10, 2025, 9:10am UTC](https://forum.inductiveautomation.com/t/sql-error-about-subquery-when-exec-stored-procedure/102092/2 "2025-02-10T09:10:30Z")

</div>

The error message translates to,

```python
The subquery returned multiple values.
This is not allowed when the subquery follows
= , != , < , <= , > , >= or when used as an expression.

```

Can you think of any reason the subquery is returning only one record in SQL Management Studio but returns multiples when run from an Ignition named query?

---

<div class="post-metadata">

**Author:** ![Raphael](https://avatars.discourse-cdn.com/v4/letter/r/edb3f5/32.png) [@Raphael](https://forum.inductiveautomation.com/u/Raphael)\
**Post date:** [February 10, 2025, 9:49am UTC](https://forum.inductiveautomation.com/t/sql-error-about-subquery-when-exec-stored-procedure/102092/3 "2025-02-10T09:49:06Z")

</div>

I understood where is the error but I don't understand why I get an error with Ignition and not with SqlServer.

In fact, I check the parameter value in the beginning of the procedure. It's here I have a subquery.

 ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/0/1/01def5123deb699dcc68ed8fd62c7d87166dcbd8.png)

Is the Java SQL driver who cause ths error ?  
How can I get work around ?

---

<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:** [February 10, 2025, 9:54am UTC](https://forum.inductiveautomation.com/t/sql-error-about-subquery-when-exec-stored-procedure/102092/4 "2025-02-10T09:54:11Z")

</div>

Can you post the runAction code that calls the named query? (Post formatted code - not pictures of code!)

---

<div class="post-metadata">

**Author:** ![Raphael](https://avatars.discourse-cdn.com/v4/letter/r/edb3f5/32.png) [@Raphael](https://forum.inductiveautomation.com/u/Raphael)\
**Post date:** [February 10, 2025, 10:08am UTC](https://forum.inductiveautomation.com/t/sql-error-about-subquery-when-exec-stored-procedure/102092/5 "2025-02-10T10:08:41Z")

</div>

There is nothing special I think, the logic is rather in the stored procedure

```python

	system.db.runNamedQuery("FinDeRun",{
		"IdPack": self.view.params.Line,
		"Utilisateur":self.session.props.address
	})
	

```

And below is the named query

```python
EXEC IHM_Packing_FinDeRun :IdPack , :Utilisateur 

```

The query type is on "Update Query"  
 ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/b/7/b75baa93bad2dd73c9ded21be548bcaf0fa3cb6a.png)

The database is on SQL Server 2005

---

<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:** [February 10, 2025, 11:33am UTC](https://forum.inductiveautomation.com/t/sql-error-about-subquery-when-exec-stored-procedure/102092/6 "2025-02-10T11:33:17Z")

</div>

I've never had to use a stored procedure in an Ignition application so I might not be able to help much. ~~I looked through the forum at related posts and I wonder if you should set the named query Query Type to "Query" rather than "Update". It should be a simple test.~~  
[Edit] I thought the SQL script was in the stored procedure. It looks like it was in the named query so my answer wasn't relevant.

---

<div class="post-metadata">

**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:** [February 10, 2025, 12:27pm UTC](https://forum.inductiveautomation.com/t/sql-error-about-subquery-when-exec-stored-procedure/102092/7 "2025-02-10T12:27:06Z")

</div>

That isn't a SQL query. It is a SQL script. Java's vanilla JDBC doesn't support SQL scripts, just single SQL **statements**.

Because of this, testing in SSMS is not valid for JDBC in Ignition. Some drivers will do it (with caveats for named queries), but if you are actually putting `@variable` or `BEGIN` and `END`, or any similar structures in Ignition, you are on your own.

Also note that stored procedures must have a consistent return. Either a single query status (normally a row count for an update/insert/delete) or a single dataset. If your SP logic is running an insert then a final SELECT, that will make two returns.

---

<div class="post-metadata">

**Author:** ![Raphael](https://avatars.discourse-cdn.com/v4/letter/r/edb3f5/32.png) [@Raphael](https://forum.inductiveautomation.com/u/Raphael)\
**Post date:** [February 10, 2025, 12:50pm UTC](https://forum.inductiveautomation.com/t/sql-error-about-subquery-when-exec-stored-procedure/102092/8 "2025-02-10T12:50:45Z")

</div>

I tried to set the query type to "Query" but I have the same issue.

It's not ideal but I hard coded the possible value of my parameter in the stored procedure to solve the problem.

Thanks for your help

---

<div class="post-metadata">

**Author:** ![Raphael](https://avatars.discourse-cdn.com/v4/letter/r/edb3f5/32.png) [@Raphael](https://forum.inductiveautomation.com/u/Raphael)\
**Post date:** [February 10, 2025, 12:51pm UTC](https://forum.inductiveautomation.com/t/sql-error-about-subquery-when-exec-stored-procedure/102092/9 "2025-02-10T12:51:29Z")

</div>

Ok, I understand.  
Thank you for the clarification.