SQL Query in Expression Item

Back in the days of FactorySQL, I could write a SQL query that executed a stored procedure and returned the value, as a string:
DECLARE @newSortID char(15)
– Second Paramater value can not be more than 15 characters
EXEC [dbo].[UFGeneratedKey] ‘098’, ‘tblSortationKPI’, @newSortID OUTPUT
Select @newSortID

This value was placed in a string tag, called RecordID, and passed to the database. No problems.

But now, with Ignition, it only returns ‘1’, which is the “success” result value, not the value itself. In researching this anomaly, I have discovered in the Help pages that, in Ignition, the SQL query must return a numerical value.

What gives? This is a MAJOR show stopper for us migrating from FSQL to Ignition. So any guidance on how to resolve this would be greatly appreciated. RecordID MUST be a 15 character value.

Try SET NOCOUNT ON - either in the stored procedure itself or in the query.

I put this in the SQL Query:

SET NOCOUNT ON
DECLARE @newSortID char(15)
– Second Paramater value can not be more than 15 characters
EXEC [dbo].[UFGeneratedKey] ‘098’, ‘tblSortationKPI’, @newSortID OUTPUT
Select @newSortID

And it gave me an evaluation error. Is that because it can’t accept a string result value?

I can’t modify the sp because it’s locked out and I would need an act of God to modify it.

Let’s back up - can you try running the modified query in the DB query browser (Tools -> Database Query Browser) directly? See if that returns correctly; if it throws some kind of SQL error then that leads in one direction, if it works correctly then we know it’s something with the query tag.

Is there any reason you can’t script it with createSProcCall ?

Probably because I don’t know how to script yet.:grin:

1 Like

I tried loading it in the DB Query Browser but it says it can’t be executed.

But if I run this as a query in SQL, it returns a value, as it should.

Continued exposure to Ignition will help fix that... :grinning:

In looking at the scripting examples, I’m not sure how to call the script so that it runs only when I need that value for a record insertion in SQL.

Currently, in FSQL, this query gets a RecordID value that is recorded with the rest of the row in a table every 5 minutes as long as the trigger is active, as part of a standard transaction group. The sp generates a unique RecordID since RecordID is the primary key.

There’s also the Stored Procedure Transaction Group… (-: