# Table type input parameter for stored procedure calls **URL:** \ **Category:** Ignition\ **Tags:** ignition79\ **Created:** [July 26, 2018, 7:36pm UTC](https://forum.inductiveautomation.com/t/table-type-input-parameter-for-stored-procedure-calls/18990 "2018-07-26T19:36:13Z")\ **Posts on this page:** 11\ **Page:** 1
**Author:** ![brian.kehn](https://avatars.discourse-cdn.com/v4/letter/b/bbe5ce/32.png) [@brian.kehn](https://forum.inductiveautomation.com/u/brian.kehn)\ **Post date:** [July 26, 2018, 7:36pm UTC](https://forum.inductiveautomation.com/t/table-type-input-parameter-for-stored-procedure-calls/18990/1 "2018-07-26T19:36:13Z")
Ignition Version 7.9.5 Sql Server 2014 Good afternoon everyone. Does anyone know how to pass a table type input parameter to the “createSProcCall” object when scripting? I’ve used the system.db.execSProcCall(call) quite a few times but only with one or two input parameters of datatype VARCHAR or INTEGER. But I’d like to pass an entire table/dataset to a stored procedure now, and it looks like it isn’t supported by these system.db commands. ```auto call = system.db.createSProcCall("dbo.UpsertRecipe","myDatabase") call.registerInParam("RecipeID", system.db.INTEGER, RecipeID) call.registerInParam("RecipeName", system.db.VARCHAR, RecipeName) call.registerInParam("SoftwareNumVer", system.db.VARCHAR, SoftwareNumVer) call.registerInParam("PartID", system.db.INT, PartID) call.registerInParam("RecipeParams", ------------, RecipeParams) call.registerInParam("UserID", system.db.VARCHAR, UserID) system.db.execSProcCall(call) resultSet = call.getResultSet() ``` The Ignition help file for “system.db.createSProcCall” shows the supported type codes, and I don’t see anything that looks like a dataset. [Here](https://docs.inductiveautomation.com/display/DOC79/system.db.createSProcCall) ---
**Author:** ![paul-griffith](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/paul-griffith/32/37249_2.png) [@paul-griffith](https://forum.inductiveautomation.com/u/paul-griffith)\ **Post date:** [July 26, 2018, 7:37pm UTC](https://forum.inductiveautomation.com/t/table-type-input-parameter-for-stored-procedure-calls/18990/2 "2018-07-26T19:37:18Z")
You can’t store an arbitrarily complex type like a dataset or array into (most) SQL flavors. You’ll have to insert each row of the dataset individually. ---
**Author:** ![brian.kehn](https://avatars.discourse-cdn.com/v4/letter/b/bbe5ce/32.png) [@brian.kehn](https://forum.inductiveautomation.com/u/brian.kehn)\ **Post date:** [July 26, 2018, 8:04pm UTC](https://forum.inductiveautomation.com/t/table-type-input-parameter-for-stored-procedure-calls/18990/3 "2018-07-26T20:04:41Z")
I wonder if I’m trying to pass “table-valued parameters”. Here is a link. [Using table valued parameters](https://docs.microsoft.com/en-us/sql/relational-databases/tables/use-table-valued-parameters-database-engine?view=sql-server-2017) I’m not sure I follow what you were meaning PGriffith. Stored procedures seem to be able to accept input parameters that are tables, but are you saying that it needs to be a consistent/set dimension? If so, lets assume that my ignition dataset, (that somehow needs to be altered and passed to the stored procedure) is always a 6 x 6. ---
**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:** [July 26, 2018, 9:09pm UTC](https://forum.inductiveautomation.com/t/table-type-input-parameter-for-stored-procedure-calls/18990/4 "2018-07-26T21:09:12Z")
It’s theoretically possible with [access under the hood](https://stackoverflow.com/questions/40387895/using-table-valued-parameters-with-sql-server-jdbc#41265546) to the JDBC driver. I don’t know that you’d be able to do it in Ignition, and even if so, only from gateway scope. Good Luck. ---
**Author:** ![brian.kehn](https://avatars.discourse-cdn.com/v4/letter/b/bbe5ce/32.png) [@brian.kehn](https://forum.inductiveautomation.com/u/brian.kehn)\ **Post date:** [August 3, 2018, 8:48pm UTC](https://forum.inductiveautomation.com/t/table-type-input-parameter-for-stored-procedure-calls/18990/5 "2018-08-03T20:48:04Z")
Thanks for the link Phil. So I tried to lobby for not getting under the hood, but unfortunately the customer would like to keep their stored procedure input in the table-valued parameter format. Assuming I can google my way through writing the Java code, could someone explain where I should paste the code? Also, why does it have to be Gateway scoped? All my other stored procedures using the “system.db.createSProcCall()” are in Client scoped tag change scripts. ---
**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:** [August 3, 2018, 9:11pm UTC](https://forum.inductiveautomation.com/t/table-type-input-parameter-for-stored-procedure-calls/18990/6 "2018-08-03T21:11:39Z")
> [@brian.kehn](#): > > Also, why does it have to be Gateway scoped? Because you have to access the classloader for the MS JDBC driver to get at the classes you need, and those only exist in the gateway. ---
**Author:** ![brian.kehn](https://avatars.discourse-cdn.com/v4/letter/b/bbe5ce/32.png) [@brian.kehn](https://forum.inductiveautomation.com/u/brian.kehn)\ **Post date:** [August 8, 2018, 7:00pm UTC](https://forum.inductiveautomation.com/t/table-type-input-parameter-for-stored-procedure-calls/18990/7 "2018-08-08T19:00:30Z")
Can you show me a quick example of how to “insert each row of the dataset individually”? I don’t see a datatype that could work for a row in the list of usable datatypes for “createSProcCall” (ex: list, touple, array). ```auto newRow = [1, "string1",300098,"string2"] call = system.db.createSProcCall("dbo.UpsertRecipe","myDB") call.registerInParam("newRow", system.db.ARRAY, newRow) ``` Something like this (although ARRAY is not included in the list of datatypes that are usable with createSProcCall). ---
**Author:** ![brian.kehn](https://avatars.discourse-cdn.com/v4/letter/b/bbe5ce/32.png) [@brian.kehn](https://forum.inductiveautomation.com/u/brian.kehn)\ **Post date:** [August 8, 2018, 11:04pm UTC](https://forum.inductiveautomation.com/t/table-type-input-parameter-for-stored-procedure-calls/18990/8 "2018-08-08T23:04:54Z")
Would it be possible to accomplish this in Jython instead of Java? ```auto def testSPCall(): #java.lang.Object #org.python.core.PyObject from com.ziclix.python.sql.Procedure import zxJDBC #create database connection d,u,p,v = "jdbc:sqlserver://10.15.4.246\SQLEXPRESS", "sa", "password123", "com.microsoft.sqlserver.jdbc.SQLServerD‌​river" db = zxJDBC.connect(d,u,p,v) #Go get my table #Pull out Array 1, Array 2, Array 3 to be used as input parameters #Open a cursur c = db.cursor() c.callproc(("SensorManufacturing","dbo","dbo.UpsertRecipe"), ["Array1","Array2","Array3"]) #close connection c.close() ``` In the IA manual it mentions other datatypes like Other, Java-Object and Array. So I’m thinking this might be possible. [IA manual CreateSProcCall()](https://docs.inductiveautomation.com/display/DOC79/system.db.createSProcCall) ![](https://us1.discourse-cdn.com/inductiveautomation/original/2X/f/fe043ddcb0603c83de7a52334df2041523f3e2f6.PNG) ---
**Author:** ![paul-griffith](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/paul-griffith/32/37249_2.png) [@paul-griffith](https://forum.inductiveautomation.com/u/paul-griffith)\ **Post date:** [August 9, 2018, 5:32pm UTC](https://forum.inductiveautomation.com/t/table-type-input-parameter-for-stored-procedure-calls/18990/9 "2018-08-09T17:32:38Z")
> [@brian.kehn](#): > > Can you show me a quick example of how to "insert each row of the dataset individually"? I don't see a datatype that could work for a row in the list of usable datatypes for "createSProcCall" (ex: list, touple, array) Sorry - my terminology was a bit misleading (this whole realm is full of overloaded terms). What I meant at the time was "insert each row into the db as a separate insert statement" - I hadn't really noticed the way your stored procedure was set up. So, just loop through all of the rows of your table, running the stored procedure call multiple times (and tag each column to insert as a separate stored procedure param). That's still going to be the simplest solution, by far. If you absolutely have to get this done, your next simplest solution is going to be writing a custom module. Yes, really. Failing that, it's theoretically possible to get your own connection object to construct a stored procedure call yourself, but it's far from trivial - you'll need to get the root GatewayContext, then get the DatasourceManager, then create your own connection object to use. Our design services department could definitely help you out with the latter two options, but, really, just modifying the procedure (or creating a new one) and calling it successively is _by far_ the best option. ---
**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:** [August 9, 2018, 5:51pm UTC](https://forum.inductiveautomation.com/t/table-type-input-parameter-for-stored-procedure-calls/18990/10 "2018-08-09T17:51:55Z")
> [@paul-griffith](#): > > Failing that, it's theoretically possible to get your own connection object to construct a stored procedure call yourself, but it's far from trivial - you'll need to get the root GatewayContext, then get the DatasourceManager, then create your own connection object to use. It's worse than that... The Datasource Manager doesn't give out "real" connections, but connections from its pooling mechanism. (Apache, iirc). I've poked at that for other DB-vendor-specific features and found that the underlying connection is wrapped up so tightly that you have to use reflection to get at protected fields. Seriously unfun and fragile. ---
**Author:** ![brian.kehn](https://avatars.discourse-cdn.com/v4/letter/b/bbe5ce/32.png) [@brian.kehn](https://forum.inductiveautomation.com/u/brian.kehn)\ **Post date:** [August 17, 2018, 3:59pm UTC](https://forum.inductiveautomation.com/t/table-type-input-parameter-for-stored-procedure-calls/18990/11 "2018-08-17T15:59:44Z")
Ok thanks guys. We’re changing the procs instead.