# Error When Calling Stored Procedure **URL:** \ **Category:** Ignition\ **Tags:** perspective\ **Created:** [May 30, 2023, 1:31pm UTC](https://forum.inductiveautomation.com/t/error-when-calling-stored-procedure/74293 "2023-05-30T13:31:24Z")\ **Posts on this page:** 12\ **Page:** 1
**Author:** ![msitson](https://avatars.discourse-cdn.com/v4/letter/m/3ab097/32.png) [@msitson](https://forum.inductiveautomation.com/u/msitson)\ **Post date:** [May 30, 2023, 1:31pm UTC](https://forum.inductiveautomation.com/t/error-when-calling-stored-procedure/74293/1 "2023-05-30T13:31:24Z")
I am trying to make a call to a stored procedure to insert a record, but I am receiving a java.lang.Exception: Error executing system.db.execSProcCall(). I have seen couple of older posts with similar issues, but no resolution to the problem. I am using version 8.1.23. This is a snippet of the script: ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/8/c/8c888b1c4d135c076c2cf472268150733fcf9d72.png) This is the error I am receiving: ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/9/5/95d31349f613de59cf0b1df1c6e64e21c52da697.png) ---
**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:** [May 30, 2023, 1:52pm UTC](https://forum.inductiveautomation.com/t/error-when-calling-stored-procedure/74293/2 "2023-05-30T13:52:51Z")
That is not the complete error. You may need to look in the gateway log to get it. There should be one or more "Caused by" sections with additional detail. ---
**Author:** ![msitson](https://avatars.discourse-cdn.com/v4/letter/m/3ab097/32.png) [@msitson](https://forum.inductiveautomation.com/u/msitson)\ **Post date:** [May 30, 2023, 1:55pm UTC](https://forum.inductiveautomation.com/t/error-when-calling-stored-procedure/74293/3 "2023-05-30T13:55:06Z")
This is the full error received in the logs. I tried looking through it, but to be honest I still don't have a clue what could be causing the error. ```auto com.inductiveautomation.ignition.common.script.JythonExecException: Traceback (most recent call last): File "", line 91, in runAction at com.inductiveautomation.ignition.common.script.builtin.AbstractDBUtilities.error(AbstractDBUtilities.java:392) at com.inductiveautomation.ignition.common.script.builtin.AbstractDBUtilities.execSProcCall(AbstractDBUtilities.java:530) at jdk.internal.reflect.GeneratedMethodAccessor686.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: Error executing system.db.execSProcCall() at org.python.core.Py.JavaError(Py.java:547) at com.inductiveautomation.ignition.common.script.builtin.AbstractDBUtilities.error(AbstractDBUtilities.java:392) at com.inductiveautomation.ignition.common.script.builtin.AbstractDBUtilities.execSProcCall(AbstractDBUtilities.java:530) at jdk.internal.reflect.GeneratedMethodAccessor686.invoke(Unknown Source) at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source) at java.base/java.lang.reflect.Method.invoke(Unknown Source) at org.python.core.PyReflectedFunction. __call__ (PyReflectedFunction.java:190) at com.inductiveautomation.ignition.common.script.ScriptManager$ReflectedInstanceFunction. __call__ (ScriptManager.java:552) at org.python.core.PyObject. __call__ (PyObject.java:461) at org.python.core.PyObject. __call__ (PyObject.java:465) at org.python.pycode._pyx104453.runAction$1(:192) at org.python.pycode._pyx104453.call_function() at org.python.core.PyTableCode.call(PyTableCode.java:173) at org.python.core.PyBaseCode.call(PyBaseCode.java:306) at org.python.core.PyFunction.function ___call__ (PyFunction.java:474) at org.python.core.PyFunction. __call__ (PyFunction.java:469) at org.python.core.PyFunction. __call__ (PyFunction.java:464) at com.inductiveautomation.ignition.common.script.ScriptManager.runFunction(ScriptManager.java:846) at com.inductiveautomation.ignition.common.script.ScriptManager.runFunction(ScriptManager.java:828) at com.inductiveautomation.ignition.gateway.project.ProjectScriptLifecycle$TrackingProjectScriptManager.runFunction(ProjectScriptLifecycle.java:832) at com.inductiveautomation.ignition.common.script.ScriptManager$ScriptFunctionImpl.invoke(ScriptManager.java:1009) at com.inductiveautomation.ignition.gateway.project.ProjectScriptLifecycle$AutoRecompilingScriptFunction.invoke(ProjectScriptLifecycle.java:897) at com.inductiveautomation.perspective.gateway.script.ScriptFunctionHelper.invoke(ScriptFunctionHelper.java:147) at com.inductiveautomation.perspective.gateway.action.ScriptAction.runAction(ScriptAction.java:74) at com.inductiveautomation.perspective.gateway.action.ActionDecorator.runAction(ActionDecorator.java:18) at com.inductiveautomation.perspective.gateway.action.SecuredAction.runAction(SecuredAction.java:44) at com.inductiveautomation.perspective.gateway.model.ActionCollection$ActionSequence$ExecuteActionsTask.lambda$call$0(ActionCollection.java:263) at com.inductiveautomation.perspective.gateway.api.LoggingContext.mdc(LoggingContext.java:54) at com.inductiveautomation.perspective.gateway.model.ActionCollection$ActionSequence$ExecuteActionsTask.call(ActionCollection.java:252) at com.inductiveautomation.perspective.gateway.model.ActionCollection$ActionSequence$ExecuteActionsTask.call(ActionCollection.java:221) at com.inductiveautomation.perspective.gateway.threading.BlockingTaskQueue$TaskWrapper.run(BlockingTaskQueue.java:154) at java.base/java.util.concurrent.Executors$RunnableAdapter.call(Unknown Source) at java.base/java.util.concurrent.FutureTask.run(Unknown Source) at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source) at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source) at com.inductiveautomation.perspective.gateway.threading.BlockingWork$BlockingWorkRunnable.run(BlockingWork.java:58) at java.base/java.lang.Thread.run(Unknown Source) Caused by: org.python.core.PyException: java.lang.Exception: java.lang.Exception: Error executing system.db.execSProcCall() ... 37 common frames omitted Caused by: java.lang.Exception: Error executing system.db.execSProcCall() ... 36 common frames omitted Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: The index 17 is out of range. at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDriverError(SQLServerException.java:228) at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.setterGetParam(SQLServerPreparedStatement.java:1124) at com.microsoft.sqlserver.jdbc.SQLServerCallableStatement.setObject(SQLServerCallableStatement.java:1567) at org.apache.commons.dbcp2.DelegatingCallableStatement.setObject(DelegatingCallableStatement.java:1236) at org.apache.commons.dbcp2.DelegatingCallableStatement.setObject(DelegatingCallableStatement.java:1236) at com.inductiveautomation.ignition.gateway.datasource.SRConnectionWrapper$SRCallableStatement.setObject(SRConnectionWrapper.java:1607) at com.inductiveautomation.ignition.gateway.datasource.query.SQLType.setParam(SQLType.java:179) at com.inductiveautomation.ignition.gateway.script.GatewayDBUtilities._callSProc(GatewayDBUtilities.java:312) at com.inductiveautomation.ignition.gateway.script.GatewayDBUtilities._call(GatewayDBUtilities.java:382) at com.inductiveautomation.ignition.common.script.builtin.AbstractDBUtilities.execSProcCall(AbstractDBUtilities.java:528) ... 34 common frames omitted ``` ---
**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:** [May 30, 2023, 1:57pm UTC](https://forum.inductiveautomation.com/t/error-when-calling-stored-procedure/74293/4 "2023-05-30T13:57:59Z")
> [@msitson](#): > > `The index 17 is out of range.` Assuming you really have all your parameter names and types correct, this can indicate a broken JDBC driver. The last time I encountered this, the work-around was to use parameter indices instead of names. ---
**Author:** ![msitson](https://avatars.discourse-cdn.com/v4/letter/m/3ab097/32.png) [@msitson](https://forum.inductiveautomation.com/u/msitson)\ **Post date:** [May 30, 2023, 2:08pm UTC](https://forum.inductiveautomation.com/t/error-when-calling-stored-procedure/74293/5 "2023-05-30T14:08:19Z")
I tried using parameter indices instead, but I am still receiving the same error. I appreciate the suggestion though. I am not sure why it would be indicating that index 17 is out of range as I have 18 parameters that can be passed to this stored procedure. ---
**Author:** ![Tim\_Carlton](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/tim_carlton/32/52835_2.png) [@Tim\_Carlton](https://forum.inductiveautomation.com/u/Tim_Carlton)\ **Post date:** [May 30, 2023, 2:12pm UTC](https://forum.inductiveautomation.com/t/error-when-calling-stored-procedure/74293/6 "2023-05-30T14:12:43Z")
Your stored procedure is expecting 18 parameters. You are passing 16 parameters. It is giving you an out-of-range error on parameter 17. What do you expect to happen when you pass only 16 parameters? Does your SPROC support optional parameters? Can you add two more parameters with default values or NULL? ---
**Author:** ![lrose](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/lrose/32/62724_2.png) [@lrose](https://forum.inductiveautomation.com/u/lrose)\ **Post date:** [May 30, 2023, 2:17pm UTC](https://forum.inductiveautomation.com/t/error-when-calling-stored-procedure/74293/7 "2023-05-30T14:17:06Z")
When using the system functions for a SProc call you must register all paramters (even if their values are NULL). You must also register any return parameters needed. If you haven't registered all parameters input or return, you will run into issues. ---
**Author:** ![msitson](https://avatars.discourse-cdn.com/v4/letter/m/3ab097/32.png) [@msitson](https://forum.inductiveautomation.com/u/msitson)\ **Post date:** [May 30, 2023, 2:23pm UTC](https://forum.inductiveautomation.com/t/error-when-calling-stored-procedure/74293/8 "2023-05-30T14:23:29Z")
Apologies, but maybe I misunderstood how optional parameters work within a stored procedure. If a parameter is already initialized with a value, I thought I wouldn't need to pass a value for it. For example this is what my stored procedure header looked like: ```auto CREATE PROCEDURE SubmissionInsertUpdate ( @StatementType NVARCHAR(20) = '', @SubmissionId int, @SubmissionDate datetime2(7) = null, @ImplementationDate datetime2(7) = null, @ImprovementType int = null, @TeamLeaderId int = null, @OriginatorId int = null, @CostSavings money = null, @ManagerId int = null, @DepartmentId int = null, @LocationArea nvarchar(max) = null, @ImprovementDesc nvarchar(max) = null, @WorkOrderRefId nvarchar(max) = null, @SavingTypes int = null, @AfterDescription nvarchar(max) = null, @BeforeDescription nvarchar(max) = null, @SubmissionType int = null, @TimeStamp datetime2(7) = SYSDATETIME) ``` ---
**Author:** ![Tim\_Carlton](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/tim_carlton/32/52835_2.png) [@Tim\_Carlton](https://forum.inductiveautomation.com/u/Tim_Carlton)\ **Post date:** [May 30, 2023, 2:29pm UTC](https://forum.inductiveautomation.com/t/error-when-calling-stored-procedure/74293/9 "2023-05-30T14:29:55Z")
> [@msitson](#): > > If a parameter is already initialized with a value, I thought I wouldn't need to pass a value for it That seems like a reasonable assumption and is probably where I would have started. Optional parameters are the kinds of things that don't interoperate all that well and passing them through various drivers and runtime environments and languages all developed by different organizations - it only takes one team in that chain to put "support optional parameters" on the wishlist instead of the must-have list for it to not work. And give the type of error that you are seeing. > [@lrose](#): > > When using the system functions for a SProc call you must register all paramters (even if their values are NULL). You must also register any return parameters needed Sounds like @lrose has seen this before in this environment. ---
**Author:** ![lrose](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/lrose/32/62724_2.png) [@lrose](https://forum.inductiveautomation.com/u/lrose)\ **Post date:** [May 30, 2023, 2:32pm UTC](https://forum.inductiveautomation.com/t/error-when-calling-stored-procedure/74293/10 "2023-05-30T14:32:30Z")
> [@Tim\_Carlton](#): > > Sounds like @lrose has seen this before in this environment. Been there, done that. Pulled out my hair, regrew it and pulled it out again. Very frustrating. Finally gave up and moved to transaction groups. Worked much better in my situation, but may not be applicable in this one. ---
**Author:** ![msitson](https://avatars.discourse-cdn.com/v4/letter/m/3ab097/32.png) [@msitson](https://forum.inductiveautomation.com/u/msitson)\ **Post date:** [May 30, 2023, 2:35pm UTC](https://forum.inductiveautomation.com/t/error-when-calling-stored-procedure/74293/11 "2023-05-30T14:35:59Z")
Thanks for all the help @pturmel @Tim_Carlton @lrose . Looks like I'll have to change my approach. ---
**Author:** ![Richard\_Foley](https://avatars.discourse-cdn.com/v4/letter/r/58f4c7/32.png) [@Richard\_Foley](https://forum.inductiveautomation.com/u/Richard_Foley)\ **Post date:** [June 9, 2025, 7:38pm UTC](https://forum.inductiveautomation.com/t/error-when-calling-stored-procedure/74293/12 "2025-06-09T19:38:54Z")
For posterity it looks like transaction groups also require optional parameters to be defined or you'll get the cryptic error: ```python Error during group execution. Error registering parameter 'xxxx'. Verify that the parameter name and type are correct. The index x is out of range. ``` What makes this especially frustrating is that the parameter referenced is one that is correctly defined.