# Stored procedure

**URL:** <https://forum.inductiveautomation.com/t/stored-procedure/18913>\
**Category:** Ignition\
**Created:** [July 20, 2018, 2:35pm UTC](https://forum.inductiveautomation.com/t/stored-procedure/18913 "2018-07-20T14:35:03Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![Matrix\_Engineering](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/matrix_engineering/32/19781_2.png) [@Matrix\_Engineering](https://forum.inductiveautomation.com/u/Matrix_Engineering)\
**Post date:** [July 20, 2018, 2:35pm UTC](https://forum.inductiveautomation.com/t/stored-procedure/18913/1 "2018-07-20T14:35:03Z")

</div>

I haven’t worked with stored procedures before, inside or outside of Ignition.

Have been given one to setup, have attached a text file with the SP, errors I get trying to run in database browser. Then tried a script to call it, and I get 36 columns returned but 0 rows, the other attchement comes from MS SQL Mgmt Studio, it has 36 columns too so appears I am getting close.

Thought the error might have been that it was returning two datasets, so split the SP into \_A and \_B but get the same errors.

First thing tried was checking this was off: [Microsoft SQL Server - "Result set was generated for update"](http://forum.inductiveautomation.com/t/microsoft-sql-server-result-set-was-generated-for-update/2499)

Any suggestions?[automation SP.txt](https://us1.discourse-cdn.com/inductiveautomation/original/2X/d/d917e0822942ddedaefe44e3d1de097d8f848d95.txt) (932 Bytes)  
[automation SP.txt](https://us1.discourse-cdn.com/inductiveautomation/original/2X/d/d917e0822942ddedaefe44e3d1de097d8f848d95.txt) (932 Bytes)  
[Copy of Automation (2).xls](https://us1.discourse-cdn.com/inductiveautomation/original/2X/d/d83e5061f9ac76351ed2fb5e044c7ffe2d947e70.xls) (25 KB)

Edit: UltimatelyI will need the barcode coming in with ? placeholder

---

<div class="post-metadata">

**Author:** ![Matrix\_Engineering](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/matrix_engineering/32/19781_2.png) [@Matrix\_Engineering](https://forum.inductiveautomation.com/u/Matrix_Engineering)\
**Post date:** [July 23, 2018, 6:56am UTC](https://forum.inductiveautomation.com/t/stored-procedure/18913/2 "2018-07-23T06:56:26Z")

</div>

Sorted,

barcode = ‘0184956500500000’  
results\_A = system.db.runPrepQuery(“exec reporting.dbo.usp\_Automation\_GetFrameDetails\_A @barcode = ?”, [barcode])  
print results\_A  
columnHeaders = system.dataset.getColumnHeaders(results\_A.getUnderlyingDataset())  
print dict(zip(columnHeaders, results\_A[0]))

results\_B = system.db.runPrepQuery(“exec reporting.dbo.usp\_Automation\_GetFrameDetails\_B @barcode = ?”, [barcode])  
print results\_B  
columnHeaders = system.dataset.getColumnHeaders(results\_B.getUnderlyingDataset())  
for x in range(0,10):  
print dict(zip(columnHeaders, results\_B[x]))

---

<div class="post-metadata">

**Author:** ![Matrix\_Engineering](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/matrix_engineering/32/19781_2.png) [@Matrix\_Engineering](https://forum.inductiveautomation.com/u/Matrix_Engineering)\
**Post date:** [August 3, 2018, 2:19pm UTC](https://forum.inductiveautomation.com/t/stored-procedure/18913/3 "2018-08-03T14:19:07Z")

</div>

OK another question, was mucking about with this earlier for about half an hour before giving up and moving on to something more productive;

in the attached image I only want a return if the result is not zero, have tried many different ways of that and I either get nothing back or get the zero results back… so in attachment I only want “630” back when z index is 21.

I have 36 columns but the majority of the time only 8 are populated with data, when I run this on a table I only want 8 columns displayed not 36 with loads of ‘0’ values

 ![](https://us1.discourse-cdn.com/inductiveautomation/original/2X/d/de2d7d81369834748bf988343744ce1185909d5f.png)