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?