# Calling Stored Procedure

**URL:** <https://forum.inductiveautomation.com/t/calling-stored-procedure/73809>\
**Category:** Ignition\
**Tags:** perspective\
**Created:** [May 16, 2023, 8:11pm UTC](https://forum.inductiveautomation.com/t/calling-stored-procedure/73809 "2023-05-16T20:11:24Z")\
**Posts on this page:** 5\
**Page:** 1

<div class="post-metadata">

**Author:** ![mdemaris](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/mdemaris/32/54639_2.png) [@mdemaris](https://forum.inductiveautomation.com/u/mdemaris)\
**Post date:** [May 16, 2023, 8:11pm UTC](https://forum.inductiveautomation.com/t/calling-stored-procedure/73809/1 "2023-05-16T20:11:25Z")

</div>

I am attempting to call an SP using a button script event, and testing using a Named Query.

Server: MSSQL  
Button Script:

```auto
def runAction(self, event):
	item = self.parent.parent.getChild("Spacer_1").getChild("Label_0").props.text
	qtyLeft = self.parent.parent.getChild("FlexContainer_2").getChild("QtyLeft_1").props.value
	userName = self.parent.parent.getChild("FlexContainer_2").getChild("Name_1").props.text
	comments = self.parent.parent.getChild("FlexContainer_2").getChild("Comments_1").props.text
	qtyTaken = self.parent.parent.getChild("FlexContainer_2").getChild("Taken_1").props.value
	transType = 1
	locId = self.view.custom.loc_id
	callsp = system.db.createSProcCall('usp_InsertSignOutTransactionTest01', tx, skipAudit)
	callsp.registerInParam(1, system.db.INTEGER, locid)
	callsp.registerInParam(2, system.db.INTEGER, transType)
	callsp.registerInParam(3, system.db.INTEGER, qtyTaken)
	callsp.registerInParam(4, system.db.VARCHAR, comments)
	callsp.registerInParam(5, system.db.INTEGER, qtyLeft)
	callsp.registerInParam(6, system.db.VARCHAR, userName)
	system.db.execSProcCall(callsp)

```

When run, the SP does not `INSERT` the data.

On the Named Query:  
This is the Query:  
`CALL [usp_InsertSignOutTransactionTest01] (:p_locid, :transType, :quantity, :comments, :rptQty, :userName)`

All numbers are `Int8`. All `NOT NULL` fields are assigned a value. I've tried using `Query Type: Query, and Update`.

The body of the SP:

```plaintext
	-- Add the parameters for the stored procedure here
	@locID INT, @typeID INT, @qty INT, @comments varchar(255), @reportedQty INT, @userName varchar(50)
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
	INSERT INTO [inv].[Transaction] (LocID, TypeID, Quantity, Comments, ReportedQty, UserName)
	VALUES (@locID, @typeID, @qty, @comments, @reportedQty, @userName)
END

```

The parameters int the Named Query are in the same order as the SP parameters.

**The Error, from testing the Named Query in the Designer:**  
`com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax near '@P0'.`

In the SSMS, this SP executes fine:

```plaintext

EXEC	@return_value = [inv].[usp_InsertSignOutTransactionTest01]
		@locID = 1,
		@typeID = 1,
		@qty = 1,
		@comments = N'none',
		@reportedQty = 1,
		@userName = N'mike'

SELECT	'Return Value' = @return_value

GO

```

---

<div class="post-metadata">

**Author:** ![mdemaris](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/mdemaris/32/54639_2.png) [@mdemaris](https://forum.inductiveautomation.com/u/mdemaris)\
**Post date:** [May 16, 2023, 10:35pm UTC](https://forum.inductiveautomation.com/t/calling-stored-procedure/73809/2 "2023-05-16T22:35:47Z")

</div>

Turns out, it helps to use the correct commands with SQL Server:  
`EXEC [inv].[usp_InsertSignOutTransactionTest01] :p_locid, :transType, :quantity, :comments, :rptQty, :userName`

I had to play around with the schema and parentheses, before I got some errors other than `syntax error`.

Then, I found that granting the MSSQL User `INSERT` permissions does not cover calling SP, so I had to add the `EXECUTE` permission.

**This solves the Named Query problem...**

BUT, not the call from the button!

---

<div class="post-metadata">

**Author:** ![mdemaris](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/mdemaris/32/54639_2.png) [@mdemaris](https://forum.inductiveautomation.com/u/mdemaris)\
**Post date:** [May 16, 2023, 10:36pm UTC](https://forum.inductiveautomation.com/t/calling-stored-procedure/73809/3 "2023-05-16T22:36:01Z")

</div>

I ditched the Call to the SP in the button event, and used a call to the Named Query, which works fine.

However, I would like to understand why the `system.db.execSProcCall(callsp)` did not work.

Thanks

---

<div class="post-metadata">

**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:** [May 16, 2023, 10:41pm UTC](https://forum.inductiveautomation.com/t/calling-stored-procedure/73809/4 "2023-05-16T22:41:12Z")

</div>

> [@mdemaris](#):
>
> When run, the SP does not `INSERT` the data.

Was there any error anywhere; in the gateway logs or the designer logs or anything?

---

<div class="post-metadata">

**Author:** ![mdemaris](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/mdemaris/32/54639_2.png) [@mdemaris](https://forum.inductiveautomation.com/u/mdemaris)\
**Post date:** [May 16, 2023, 10:51pm UTC](https://forum.inductiveautomation.com/t/calling-stored-procedure/73809/5 "2023-05-16T22:51:49Z")

</div>

Well, I'm glad you mentioned the gateway logs! I don't think I was really aware of that resource! After checking those logs, I found out why. My lack of knowledge, lol. I left the `tx and skipaudit` untouched, assuming I didn't need to do anything with them via Intellisense.

Calling the SP via the button script action works as intended, as does the SP through the Named Query.