# How things work - no feedback from stored procedure?

**URL:** <https://forum.inductiveautomation.com/t/how-things-work-no-feedback-from-stored-procedure/76256>\
**Category:** Ignition\
**Tags:** ignition81\
**Created:** [July 12, 2023, 1:50pm UTC](https://forum.inductiveautomation.com/t/how-things-work-no-feedback-from-stored-procedure/76256 "2023-07-12T13:50:32Z")\
**Posts on this page:** 13\
**Page:** 1

<div class="post-metadata">

**Author:** ![hewking32](https://avatars.discourse-cdn.com/v4/letter/h/b5ac83/32.png) [@hewking32](https://forum.inductiveautomation.com/u/hewking32)\
**Post date:** [July 12, 2023, 1:50pm UTC](https://forum.inductiveautomation.com/t/how-things-work-no-feedback-from-stored-procedure/76256/1 "2023-07-12T13:50:32Z")

</div>

hi  
I am using stored procedure to send data from Ignition to other system. I didn't get my head around how to capture the feedback from the executed stored procedure - there must be a way -right? Stored procedure itself i was told to use does not provide any value and from what i read in docs SProcCall also does not provide any feedback.

So if stored procedure itself does not provide any return value is using it like fire and forget 🤨 ?  
My code wrapped in the function looks following :

```auto
def TriggerStoredProcedure(message):
	call = system.db.createSProcCall("dbo.StoredProcedureName", "DatabaseConnectionName")
	call.registerInParam(1, system.db.INTEGER, 1)
	call.registerInParam(2, system.db.VARCHAR,message)
	call.registerInParam(3, system.db.INTEGER, 2)
	call.registerReturnParam(system.db.INTEGER)
	system.db.execSProcCall(call)

```

i believe there is no need for call.registerReturnParam(system.db.INTEGER) line.  
Thanks for any help  
@pturmel any chance for a comment ?

---

<div class="post-metadata">

**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 12, 2023, 1:52pm UTC](https://forum.inductiveautomation.com/t/how-things-work-no-feedback-from-stored-procedure/76256/2 "2023-07-12T13:52:42Z")

</div>

Hmm. Not sure. I would think `system.db.execSProcCall()` itself might throw an exception if the proc is rejected up front.

---

<div class="post-metadata">

**Author:** ![hewking32](https://avatars.discourse-cdn.com/v4/letter/h/b5ac83/32.png) [@hewking32](https://forum.inductiveautomation.com/u/hewking32)\
**Post date:** [July 12, 2023, 1:53pm UTC](https://forum.inductiveautomation.com/t/how-things-work-no-feedback-from-stored-procedure/76256/3 "2023-07-12T13:53:31Z")

</div>

yes i do get exception that parameters were missing

---

<div class="post-metadata">

**Author:** ![hewking32](https://avatars.discourse-cdn.com/v4/letter/h/b5ac83/32.png) [@hewking32](https://forum.inductiveautomation.com/u/hewking32)\
**Post date:** [July 12, 2023, 1:54pm UTC](https://forum.inductiveautomation.com/t/how-things-work-no-feedback-from-stored-procedure/76256/4 "2023-07-12T13:54:31Z")

</div>

but if stored procedure did not execute for any other reason like permission, connection etc i could use some logic to resend the data

---

<div class="post-metadata">

**Author:** ![hewking32](https://avatars.discourse-cdn.com/v4/letter/h/b5ac83/32.png) [@hewking32](https://forum.inductiveautomation.com/u/hewking32)\
**Post date:** [July 12, 2023, 1:59pm UTC](https://forum.inductiveautomation.com/t/how-things-work-no-feedback-from-stored-procedure/76256/5 "2023-07-12T13:59:06Z")

</div>

error message below when i removed 1 parameter

 ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/8/0/80779f89f3886786333b1db5589d0ed030bbd19e.png)

---

<div class="post-metadata">

**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 12, 2023, 2:00pm UTC](https://forum.inductiveautomation.com/t/how-things-work-no-feedback-from-stored-procedure/76256/6 "2023-07-12T14:00:02Z")

</div>

You're blazing a trail here. I don't recall ever deploying a stored procedure call that didn't return anything at all.

---

<div class="post-metadata">

**Author:** ![hewking32](https://avatars.discourse-cdn.com/v4/letter/h/b5ac83/32.png) [@hewking32](https://forum.inductiveautomation.com/u/hewking32)\
**Post date:** [July 12, 2023, 2:04pm UTC](https://forum.inductiveautomation.com/t/how-things-work-no-feedback-from-stored-procedure/76256/7 "2023-07-12T14:04:01Z")

</div>

i agree it sounds strange but i was given this stored procedure and i was told that it does not return anything for legacy code purposes. So i guess my sanity check has passed and all i can do is use try: except wrapper which would catch lack of parameter passed.

---

<div class="post-metadata">

**Author:** ![zacht](https://avatars.discourse-cdn.com/v4/letter/z/ea5d25/32.png) [@zacht](https://forum.inductiveautomation.com/u/zacht)\
**Post date:** [July 12, 2023, 2:09pm UTC](https://forum.inductiveautomation.com/t/how-things-work-no-feedback-from-stored-procedure/76256/8 "2023-07-12T14:09:29Z")

</div>

> [@hewking32](#):
>
> but if stored procedure did not execute for any other reason like permission, connection etc i could use some logic to resend the data

Could you create a "wrapper" stored procedure on the same server that:

1. Calls the existing stored procedure
2. Checks that the the task was accomplished
3. Returns some value based on step 2?

---

<div class="post-metadata">

**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:** [July 12, 2023, 2:13pm UTC](https://forum.inductiveautomation.com/t/how-things-work-no-feedback-from-stored-procedure/76256/9 "2023-07-12T14:13:46Z")

</div>

Stored procedures always return a value. If no return statement is provided then the default return is 0.

Due to the way that `system.db.execSProcCall()` works you must register a return param. However, much like `system.tag.writeBlocking()` and similar functions providing a return value, that doesn't mean you have to use it.

The quirkiness of SProcCall and its helpers is why I have moved away from SP when Ignition is involved, and if I want to execute a SP, I will just create a Named Query to execute it and move on.

---

<div class="post-metadata">

**Author:** ![hewking32](https://avatars.discourse-cdn.com/v4/letter/h/b5ac83/32.png) [@hewking32](https://forum.inductiveautomation.com/u/hewking32)\
**Post date:** [July 13, 2023, 10:46am UTC](https://forum.inductiveautomation.com/t/how-things-work-no-feedback-from-stored-procedure/76256/10 "2023-07-13T10:46:12Z")

</div>

Thanks for help. I read somewhere in docs that preferred way to call stored procedure is with `system.db.execSProcCall()`. Can you please elaborate on the benefit of using named query - what steps are needed and what benefits are gained doing this?

---

<div class="post-metadata">

**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:** [July 13, 2023, 1:43pm UTC](https://forum.inductiveautomation.com/t/how-things-work-no-feedback-from-stored-procedure/76256/11 "2023-07-13T13:43:00Z")

</div>

> [@hewking32](#):
>
> Can you please elaborate on the benefit of using named query

Sure.

You would create a named query that looked like this:

```SQL
EXECUTE dbo.StoredProcedureName :param1 , :message , :param2

```

You would call it from script like this:

```python
def triggerStoredProcedure(message):
    system.db.runNamedQuery('YourNamedQuery',{'param1':1,'message':message,'param2':2})

```

And...You're done, much simpler. It's truly fire and forget in this case as you're unconcerned with the return value, though it will also work with stored procedures that return result sets.

You get all of the advantages of a named query, and you don't run into the eccentricities that come with creating a SPCall. Back in the days prior to named queries this was the only way to call stored procedures, IMO named queries have made the method mostly obsolete.

All of that out of the way, unless you're doing some really advanced things in the stored procedure that isn't handled so well by the JDBC driver, and those kinds of things do exist, or you have other third party software that is also using the stored procedure, I would recommend just putting the SQL directly in the named query itself. Makes things much more manageable IMO.

---

<div class="post-metadata">

**Author:** ![hewking32](https://avatars.discourse-cdn.com/v4/letter/h/b5ac83/32.png) [@hewking32](https://forum.inductiveautomation.com/u/hewking32)\
**Post date:** [July 13, 2023, 4:21pm UTC](https://forum.inductiveautomation.com/t/how-things-work-no-feedback-from-stored-procedure/76256/12 "2023-07-13T16:21:30Z")

</div>

You answered my question really well so many thanks. I am curious type - any chance you give me a gist on :

> unless you're doing some really advanced things in the stored procedure that isn't handled so well by the JDBC driver, and those kinds of things do exist,

What sort of fancy things i need to be aware of - i am working mostly with Microsoft SQL server - is it worth reading JDBC specification for microsoft server jdbc driver if time permits?

---

<div class="post-metadata">

**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:** [July 13, 2023, 4:45pm UTC](https://forum.inductiveautomation.com/t/how-things-work-no-feedback-from-stored-procedure/76256/13 "2023-07-13T16:45:02Z")

</div>

Batching operations, Cursors, lots of work with creation of and dropping of temporary tables. Anything where the query optimization is really important is most likely going to be better handled in a Stored Procedure.

For most things that you're going to do with Ignition, Named Queries will work just fine. Its the types of things that you find your self googling "How to do X" with a purpose other than a reminder of the needed syntax.