# Existing Stored Procedures MSSQL

**URL:** <https://forum.inductiveautomation.com/t/existing-stored-procedures-mssql/12732>\
**Category:** Ignition\
**Created:** [February 28, 2017, 3:38pm UTC](https://forum.inductiveautomation.com/t/existing-stored-procedures-mssql/12732 "2017-02-28T15:38:45Z")\
**Posts on this page:** 5\
**Page:** 1

<div class="post-metadata">

**Author:** ![mwaynesmith](https://avatars.discourse-cdn.com/v4/letter/m/eb8c5e/32.png) [@mwaynesmith](https://forum.inductiveautomation.com/u/mwaynesmith)\
**Post date:** [February 28, 2017, 3:38pm UTC](https://forum.inductiveautomation.com/t/existing-stored-procedures-mssql/12732/1 "2017-02-28T15:38:45Z")

</div>

We have an existing MES (MSSQL) that has numerous SP’s that basically contain recipes, component pick bins, etc. These SP’s typically require input parameters of stationID (which workcell) and PartID (which SKU). the outputs are typically good/bad (invalid combination) and a result set containing a list of picklights, or a list of recipes to run. We would want to apply these ‘lists’ and write to PLC tags for each item in the list(s). These are not time based nor really grouped, they are processed as parts are placed in the workcell.

I have not found a good source for explaining the use of Stored Procedures other than transactions in SQL bridge. I really don’t see how that would meet our needs.

Any and all suggestions, guidance much appreciated

---

<div class="post-metadata">

**Author:** ![nmudge](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/nmudge/32/3394_2.png) [@nmudge](https://forum.inductiveautomation.com/u/nmudge)\
**Post date:** [February 28, 2017, 3:44pm UTC](https://forum.inductiveautomation.com/t/existing-stored-procedures-mssql/12732/2 "2017-02-28T15:44:32Z")

</div>

Ignition has builtin scripting functions for calling stored procedures. Examples of using them are at this link: [docs.inductiveautomation.com/di … eSProcCall](https://docs.inductiveautomation.com/display/DOC79/system.db.createSProcCall)

Best,

---

<div class="post-metadata">

**Author:** ![mwaynesmith](https://avatars.discourse-cdn.com/v4/letter/m/eb8c5e/32.png) [@mwaynesmith](https://forum.inductiveautomation.com/u/mwaynesmith)\
**Post date:** [February 28, 2017, 3:49pm UTC](https://forum.inductiveautomation.com/t/existing-stored-procedures-mssql/12732/3 "2017-02-28T15:49:58Z")

</div>

Thanks. I have been attempting to make this work with the following example snippet on that page:

#This example would call a stored procedure “get\_shift\_workers” with no arguments, which returned a result set of employees for the current shift. It then pushes the resulting dataset into a Table component:

call = system.db.createSProcCall(“get\_shift\_workers”)  
system.db.execSProcCall(call)

results = call.getResultSet()  
table = event.source.parent.getComponent(“Table”)  
table.data = results

This snippet fails in the script console with: File “”, line 5, in   
NameError: name ‘event’ is not defined

I changed the sp to one in my DB and I do get the result set (once comment the event statement) but do not know how to access the data.

---

<div class="post-metadata">

**Author:** ![nmudge](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/nmudge/32/3394_2.png) [@nmudge](https://forum.inductiveautomation.com/u/nmudge)\
**Post date:** [February 28, 2017, 4:11pm UTC](https://forum.inductiveautomation.com/t/existing-stored-procedures-mssql/12732/4 "2017-02-28T16:11:13Z")

</div>

Hi mwaynesmith,

The script would only work in a window, like in an event handler on a button where the window had a table called “Table” in it.

Instead try this script in the script console:

[code]call = system.db.createSProcCall(“get\_shift\_workers”)  
system.db.execSProcCall(call)

results = call.getResultSet()

for row in system.dataset.toPyDataSet(results):  
print list(row)[/code]  
I suggest that you read about datasets in the Ignition User Manual and in my blog post about it here: [nickmudge.info/post/datasets-in-Ignition](http://nickmudge.info/post/datasets-in-Ignition)

That way you will know how to work with datasets and access data in them.  
Best,

---

<div class="post-metadata">

**Author:** ![mwaynesmith](https://avatars.discourse-cdn.com/v4/letter/m/eb8c5e/32.png) [@mwaynesmith](https://forum.inductiveautomation.com/u/mwaynesmith)\
**Post date:** [February 28, 2017, 4:28pm UTC](https://forum.inductiveautomation.com/t/existing-stored-procedures-mssql/12732/5 "2017-02-28T16:28:44Z")

</div>

Thanks, that worked.