# Transaction Group for Stored Procedures Help **URL:** \ **Category:** Ignition\ **Tags:** ignition81\ **Created:** [June 29, 2022, 12:08pm UTC](https://forum.inductiveautomation.com/t/transaction-group-for-stored-procedures-help/61346 "2022-06-29T12:08:14Z")\ **Posts on this page:** 14\ **Page:** 1
**Author:** ![mjohnson0238](https://avatars.discourse-cdn.com/v4/letter/m/4bbf92/32.png) [@mjohnson0238](https://forum.inductiveautomation.com/u/mjohnson0238)\ **Post date:** [June 29, 2022, 12:08pm UTC](https://forum.inductiveautomation.com/t/transaction-group-for-stored-procedures-help/61346/1 "2022-06-29T12:08:14Z")
I am using 8.1.0. I have watched the video for the subject matter and still dont quite understand. What I want to do is execute a stored procedure and have those columns that it returns write to a memory tag so I can them use those tags and compare them to another set of tags for a SpecAudit project that I am working on. Does this sound like something that could be achived? ---
**Author:** ![bkarabinchak.psi](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/bkarabinchak.psi/32/83567_2.png) [@bkarabinchak.psi](https://forum.inductiveautomation.com/u/bkarabinchak.psi)\ **Post date:** [June 29, 2022, 12:16pm UTC](https://forum.inductiveautomation.com/t/transaction-group-for-stored-procedures-help/61346/2 "2022-06-29T12:16:03Z")
Definitely something that can be achieved but I am not sure a transaction group is the most appropriate method of doing it. Transaction groups are similar to historian/historical data in a tag in that they target certain tags and save values on a predetermined time interval and execution condition (if tagX \> 1, then save tags A,B,C values every 1 second for instance). What I think you might want for, if you’re just trying to update a memory tag to the data returned via a stored procedure, is to setup a gateway timer script (if you want it to run every X seconds) or setup a tag change event (if you want some tag change to drive the execution) and in there you can do something like ```auto call = system.db.createSProcCall("get_shift_workers") system.db.execSProcCall(call) values = [call.getResultSet()] tags = ['some/Tag/Path'] system.tag.writeBlocking(tags, values) ``` Check out [system.db.createSProcCall - Ignition User Manual 8.1 - Ignition Documentation](https://docs.inductiveautomation.com/display/DOC81/system.db.createSProcCall) to see how to call a stored procedure via scripting. Doing this in a gateway timer script or gateway tag change event would also allow you to do any data comparisons needed. ---
**Author:** ![mjohnson0238](https://avatars.discourse-cdn.com/v4/letter/m/4bbf92/32.png) [@mjohnson0238](https://forum.inductiveautomation.com/u/mjohnson0238)\ **Post date:** [June 29, 2022, 12:32pm UTC](https://forum.inductiveautomation.com/t/transaction-group-for-stored-procedures-help/61346/3 "2022-06-29T12:32:09Z")
Alright, so here is my stored procedure that was created in SQL and how I execute in SQL ```auto exec [65M19Extruder_ItemsBySpecAndRevisionNumberForWinCC] 3709 , 1 ``` In scripting would I call it like this: ```auto system.db.execSProcCall([65M19Extruder_ItemsBySpecAndRevisionNumberForWinCC] 3709 , 1) ``` ? ---
**Author:** ![bkarabinchak.psi](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/bkarabinchak.psi/32/83567_2.png) [@bkarabinchak.psi](https://forum.inductiveautomation.com/u/bkarabinchak.psi)\ **Post date:** [June 29, 2022, 12:34pm UTC](https://forum.inductiveautomation.com/t/transaction-group-for-stored-procedures-help/61346/4 "2022-06-29T12:34:39Z")
3709 and 1 are params? You will need to use `registerInParam`. Try ```auto call = system.db.createSProcCall("get_shift_workers") call.registerInParam(1, system.db.INTEGER, 3709) call.registerInParam(2, system.db.INTEGER, 1) system.db.execSProcCall(call) values = [call.getResultSet()] tags = ['some/Tag/Path'] system.tag.writeBlocking(tags, values) ``` Take a look at the page [system.db.createSProcCall - Ignition User Manual 8.1 - Ignition Documentation](https://docs.inductiveautomation.com/display/DOC81/system.db.createSProcCall) They offer examples on multiple scenarios you might use or need ---
**Author:** ![mjohnson0238](https://avatars.discourse-cdn.com/v4/letter/m/4bbf92/32.png) [@mjohnson0238](https://forum.inductiveautomation.com/u/mjohnson0238)\ **Post date:** [June 29, 2022, 9:17pm UTC](https://forum.inductiveautomation.com/t/transaction-group-for-stored-procedures-help/61346/5 "2022-06-29T21:17:21Z")
I hate to ask but since I have a stored procedure already do I need to do this portion? 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:** [June 29, 2022, 9:19pm UTC](https://forum.inductiveautomation.com/t/transaction-group-for-stored-procedures-help/61346/6 "2022-06-29T21:19:55Z")
Yes. You’re “creating” the Ignition/JDBC object used to execute the stored procedure, not creating anything on the actual database. ---
**Author:** ![mjohnson0238](https://avatars.discourse-cdn.com/v4/letter/m/4bbf92/32.png) [@mjohnson0238](https://forum.inductiveautomation.com/u/mjohnson0238)\ **Post date:** [June 29, 2022, 9:38pm UTC](https://forum.inductiveautomation.com/t/transaction-group-for-stored-procedures-help/61346/7 "2022-06-29T21:38:19Z")
So to start the script will look like this: ```auto call = system.db.createSProcCall("[65M19Extruder_ItemsBySpecAndRevisionNumberForWinCC]") call.registerInParam(1, system.db.INTEGER, 3709) call.registerInParam(2, system.db.INTEGER, 1) system.db.execSProcCall(call) ``` ---
**Author:** ![josborn](https://avatars.discourse-cdn.com/v4/letter/j/4bbf92/32.png) [@josborn](https://forum.inductiveautomation.com/u/josborn)\ **Post date:** [June 29, 2022, 10:03pm UTC](https://forum.inductiveautomation.com/t/transaction-group-for-stored-procedures-help/61346/8 "2022-06-29T22:03:25Z")
Another option might be to run your stored procedure in a named query, then run the named query with `system.db.runNamedQuery`. It seems easier, at least to me. ---
**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:** [June 29, 2022, 10:08pm UTC](https://forum.inductiveautomation.com/t/transaction-group-for-stored-procedures-help/61346/9 "2022-06-29T22:08:02Z")
Yes, assuming that your parameters are both integer types. You would also need to register a return parameter if you are returning things from the SP. It’s very dependent on how the SP is written. I agree with @josborn a Named Query is the easiest and IMHO most reliable. ---
**Author:** ![mjohnson0238](https://avatars.discourse-cdn.com/v4/letter/m/4bbf92/32.png) [@mjohnson0238](https://forum.inductiveautomation.com/u/mjohnson0238)\ **Post date:** [June 30, 2022, 12:44pm UTC](https://forum.inductiveautomation.com/t/transaction-group-for-stored-procedures-help/61346/10 "2022-06-30T12:44:53Z")
I have been thinking about a named query also, mainly because I am more familiar with it. Right now the Stored Procedure, when ran in a Named Query, returns a table with 203 columns and 1 row. I assume i will need 203 memory tags for the data to be stored in? ---
**Author:** ![mjohnson0238](https://avatars.discourse-cdn.com/v4/letter/m/4bbf92/32.png) [@mjohnson0238](https://forum.inductiveautomation.com/u/mjohnson0238)\ **Post date:** [June 30, 2022, 12:47pm UTC](https://forum.inductiveautomation.com/t/transaction-group-for-stored-procedures-help/61346/11 "2022-06-30T12:47:51Z")
And the parameters are integers but are also variable. like 3801,1 or 3897,3 ---
**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:** [June 30, 2022, 12:47pm UTC](https://forum.inductiveautomation.com/t/transaction-group-for-stored-procedures-help/61346/12 "2022-06-30T12:47:52Z")
You can have a single tag of dataset type. This seems like something that you would do in a Gateway Tag Change Event, then you can run the query and do the comparison all in one place. What determines if the parameters change? ---
**Author:** ![mjohnson0238](https://avatars.discourse-cdn.com/v4/letter/m/4bbf92/32.png) [@mjohnson0238](https://forum.inductiveautomation.com/u/mjohnson0238)\ **Post date:** [June 30, 2022, 1:02pm UTC](https://forum.inductiveautomation.com/t/transaction-group-for-stored-procedures-help/61346/13 "2022-06-30T13:02:31Z")
Right now, our scheduling team lets the production team know what product to run and then they reference that to a listing of what product runs at what spec/rev and enters that as the machine setting in HMI. After that periodically production checks the running settings to the spec/rev. I am trying to automate that check. Since we dont have a digital input for the spec/rev, that will have to entered manually into a text field that will change parameters in the named query so a comparison can happen, spec/rev against actual values from machine tags. From there I will setup some way for them to be notified, so the issues can be addressed. ---
**Author:** ![francois.morin](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/francois.morin/32/15064_2.png) [@francois.morin](https://forum.inductiveautomation.com/u/francois.morin)\ **Post date:** [July 5, 2022, 3:33pm UTC](https://forum.inductiveautomation.com/t/transaction-group-for-stored-procedures-help/61346/14 "2022-07-05T15:33:42Z")
I have always used output parameters in my procedures to return data to a TransactionGroup. Example: ```auto CREATE PROCEDURE ABC @IN_VALUE1 INT ,@IN_VALUE2 INT ,@OUT_VALUE1 INT = NULL OUTPUT ,@OUT_VALUE2 INT = NULL OUTPUT ,@OUT_VALUE3 INT = NULL OUTPUT AS BEGIN SELECT @OUT_VALUE1 = ,@OUT_VALUE2 = ,@OUT_VALUE3 = FROM Table_XYZ END ``` After that you can map those OUTPUT parameters directly in your TransactionGroup