Triggered Expression Item problem

I am trying to create a triggered expression item that uses a SQL expression to determine the next value for my primary key. Now the primary key is a 15-character string (because of the requirements of the application). Since Ignition can only return an integer from a SQL expression, I need to create the ‘unique’ part (part of the suffix) and concatenate this to the prefix. I can go into more detail if necessary but suffice it to say, I am running a stored procedure that retrieves the next ‘unique’ value.

Here is the code:
DECLARE @newKeyVal Int
– Second Paramater value can not be more than 15 characters
EXEC [dbo].[UFGeneratedKey] ‘098’, ‘tblSortationKPI’, @newKeyVal OUTPUT

The stored procedure UFGeneratedKey takes two parameters: a plant location (in this case, the string ‘098’) and a table for which the new key will be inserted, in this case ‘tblSortationKPI’.

Originally, @newKeyVal was a char(15). This worked beautifully in FSQL but now Ignition restricts the output to be an integer.

So I run this in the Designer and it gives me a value of 1. That’s fine. First record. Should be 1.

But for all subsequent triggers, the value stays 1. If I run the stored procedure in SQL, the value goes up, like it should. So the sp is working correctly, but the output still shows 1.

So I added this:
DECLARE @newKeyVal Int
Set @newKeyVal = 777
– Second Paramater value can not be more than 15 characters
EXEC [dbo].[UFGeneratedKey] ‘098’, ‘tblSortationKPI’, @newKeyVal OUTPUT

It still produces only 1 as the output.

So I added this:
DECLARE @newKeyVal Int
Set @newKeyVal = 777
– Second Paramater value can not be more than 15 characters
EXEC [dbo].[UFGeneratedKey] ‘098’, ‘tblSortationKPI’, @newKeyVal OUTPUT
Set @newKeyVal = 123
(so @newKeyVal should be set to 123, regardless of the outcome of the sp)

And I still get 1.

What am I doing wrong that keeps the output at 1, even though the stored procedure is outputting a different number?

When you just run a stored procedure as if it is an update, the first response is the execution status, not any output parameters. You need to use the stored procedure transaction group, or script your stored procedure with system.db.createSProcCall().

Thank you, Phil. Where is the scripting done? Can it be done at the tag group level so that the script is executed each time the group is triggered?

If you are trying to do this in a transaction group, just use the Stored Procedure type of Group. Otherwise, use a Gateway Tag Change Event Script monitoring your trigger, and use system.tag.read*() and/or system.opc.read*() to get everything else you need.