# SQL Query in Expression Item **URL:** \ **Category:** Ignition\ **Tags:** ignition79\ **Created:** [May 8, 2018, 9:57pm UTC](https://forum.inductiveautomation.com/t/sql-query-in-expression-item/17966 "2018-05-08T21:57:25Z")\ **Posts on this page:** 10\ **Page:** 1
**Author:** ![NHOutbacker](https://avatars.discourse-cdn.com/v4/letter/n/13edae/32.png) [@NHOutbacker](https://forum.inductiveautomation.com/u/NHOutbacker)\ **Post date:** [May 8, 2018, 9:57pm UTC](https://forum.inductiveautomation.com/t/sql-query-in-expression-item/17966/1 "2018-05-08T21:57:25Z")
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. ---
**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 8, 2018, 10:00pm UTC](https://forum.inductiveautomation.com/t/sql-query-in-expression-item/17966/2 "2018-05-08T22:00:05Z")
Try `SET NOCOUNT ON` - either in the stored procedure itself or in the query. ---
**Author:** ![NHOutbacker](https://avatars.discourse-cdn.com/v4/letter/n/13edae/32.png) [@NHOutbacker](https://forum.inductiveautomation.com/u/NHOutbacker)\ **Post date:** [May 8, 2018, 10:08pm UTC](https://forum.inductiveautomation.com/t/sql-query-in-expression-item/17966/3 "2018-05-08T22:08:49Z")
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. ---
**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 8, 2018, 10:51pm UTC](https://forum.inductiveautomation.com/t/sql-query-in-expression-item/17966/4 "2018-05-08T22:51:44Z")
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. ---
**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:** [May 9, 2018, 1:29am UTC](https://forum.inductiveautomation.com/t/sql-query-in-expression-item/17966/5 "2018-05-09T01:29:34Z")
Is there any reason you can’t script it with [createSProcCall](https://docs.inductiveautomation.com/display/DOC79/system.db.createSProcCall) ? ---
**Author:** ![NHOutbacker](https://avatars.discourse-cdn.com/v4/letter/n/13edae/32.png) [@NHOutbacker](https://forum.inductiveautomation.com/u/NHOutbacker)\ **Post date:** [May 9, 2018, 7:02pm UTC](https://forum.inductiveautomation.com/t/sql-query-in-expression-item/17966/6 "2018-05-09T19:02:34Z")
Probably because I don’t know how to script yet.😁 ---
**Author:** ![NHOutbacker](https://avatars.discourse-cdn.com/v4/letter/n/13edae/32.png) [@NHOutbacker](https://forum.inductiveautomation.com/u/NHOutbacker)\ **Post date:** [May 9, 2018, 7:05pm UTC](https://forum.inductiveautomation.com/t/sql-query-in-expression-item/17966/7 "2018-05-09T19:05:30Z")
I tried loading it in the DB Query Browser but it says it can’t be executed. ![](https://us1.discourse-cdn.com/inductiveautomation/original/2X/a/a3556c3e7fd5f5924f62ae056162efb32637c72e.png) But if I run this as a query in SQL, it returns a value, as it should. ---
**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:** [May 9, 2018, 7:12pm UTC](https://forum.inductiveautomation.com/t/sql-query-in-expression-item/17966/8 "2018-05-09T19:12:56Z")
> [@NHOutbacker](#): > > Probably because I don't know how to script yet. Continued exposure to Ignition will help fix that... 😀 ---
**Author:** ![NHOutbacker](https://avatars.discourse-cdn.com/v4/letter/n/13edae/32.png) [@NHOutbacker](https://forum.inductiveautomation.com/u/NHOutbacker)\ **Post date:** [May 9, 2018, 7:22pm UTC](https://forum.inductiveautomation.com/t/sql-query-in-expression-item/17966/9 "2018-05-09T19:22:51Z")
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. ---
**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:** [May 9, 2018, 7:36pm UTC](https://forum.inductiveautomation.com/t/sql-query-in-expression-item/17966/10 "2018-05-09T19:36:11Z")
There’s also the Stored Procedure Transaction Group… (-: