# Return inserted key with stored procedure **URL:** \ **Category:** Ignition\ **Tags:** database, scripting, ignition81\ **Created:** [April 9, 2024, 8:29am UTC](https://forum.inductiveautomation.com/t/return-inserted-key-with-stored-procedure/88567 "2024-04-09T08:29:34Z")\ **Posts on this page:** 7\ **Page:** 1
**Author:** ![Ryan\_Shaw](https://avatars.discourse-cdn.com/v4/letter/r/dbc845/32.png) [@Ryan\_Shaw](https://forum.inductiveautomation.com/u/Ryan_Shaw)\ **Post date:** [April 9, 2024, 8:29am UTC](https://forum.inductiveautomation.com/t/return-inserted-key-with-stored-procedure/88567/1 "2024-04-09T08:29:34Z")
I'm running a MSSQL stored procedure that checks if a key exists and inserts a new row if it does not or does nothing if it does, and then returns the inserted row's ID, if any. The procedure looks like this ```auto IF @keyExists = 0 THEN INSERT INTO TABLE ``` I am struggling to return the ID to Ignition. If I run this using system.db.runUpdateQuery with getKey=1 I get the error "the statement must be executed before any results can be obtained.", which means there is no key being returned. This is still true if I update my procedure to ```auto IF @keyExists = 0 THEN INSERT INTO TABLE OUTPUT Table.ID ``` If I change the procedure to ```auto IF @keyExists = 0 THEN INSERT INTO TABLE SELECT SCOPE_IDENTITY() ``` I get the error "A result set was generated for update". I can get this to work if I use ```auto IF @keyExists = 0 THEN INSERT INTO TABLE SELECT SCOPE_IDENTITY() ``` and run it using system.db.runScalarQuery, but this is not ideal as Ignition will run the update even if I'm in read-only mode and I really want to avoid breaking that failsafe. Does anyone have any other ideas for getting an update stored procedure to return a table ID to Ignition? ---
**Author:** ![lrose](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/lrose/32/62724_2.png) [@lrose](https://forum.inductiveautomation.com/u/lrose)\ **Post date:** [April 9, 2024, 1:28pm UTC](https://forum.inductiveautomation.com/t/return-inserted-key-with-stored-procedure/88567/2 "2024-04-09T13:28:58Z")
Is this actually a stored procedure in the DB? Or are you typing this syntax in a script and trying to execute that with the system functions? ---
**Author:** ![Ryan\_Shaw](https://avatars.discourse-cdn.com/v4/letter/r/dbc845/32.png) [@Ryan\_Shaw](https://forum.inductiveautomation.com/u/Ryan_Shaw)\ **Post date:** [April 9, 2024, 1:30pm UTC](https://forum.inductiveautomation.com/t/return-inserted-key-with-stored-procedure/88567/3 "2024-04-09T13:30:26Z")
This is a stored procedure in the DB. I call it using ```auto system.db.runQuery('EXEC mySP @param1=val') ``` ---
**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:** [April 9, 2024, 1:45pm UTC](https://forum.inductiveautomation.com/t/return-inserted-key-with-stored-procedure/88567/4 "2024-04-09T13:45:45Z")
Running a stored procedure via general statement is suboptimal. Run it with Ignition's dedicated stored procedure functions. ---
**Author:** ![lrose](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/lrose/32/62724_2.png) [@lrose](https://forum.inductiveautomation.com/u/lrose)\ **Post date:** [April 9, 2024, 5:20pm UTC](https://forum.inductiveautomation.com/t/return-inserted-key-with-stored-procedure/88567/5 "2024-04-09T17:20:03Z")
Since you are executing a stored procedure You will have to modify the stored procedure to return the inserted key. You can use the dedicated stored procedure functions, but they can be "frustrating". I would recommend executing this as a Named Query. ---
**Author:** ![MSviga](https://avatars.discourse-cdn.com/v4/letter/m/3e96dc/32.png) [@MSviga](https://forum.inductiveautomation.com/u/MSviga)\ **Post date:** [April 11, 2024, 8:00am UTC](https://forum.inductiveautomation.com/t/return-inserted-key-with-stored-procedure/88567/6 "2024-04-11T08:00:04Z")
Generally speaking, what would be the better approach for getting the inserted key? Using the `getKey` parameter of `system.db.runNamedQuery()` or specifying `SCOPE_IDENTITY()` within the SQL itself? ---
**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:** [April 11, 2024, 11:48am UTC](https://forum.inductiveautomation.com/t/return-inserted-key-with-stored-procedure/88567/7 "2024-04-11T11:48:26Z")
Ignition's `getKey()` infrastructure can only get 32-bit integer keys. Larger integers are truncated to their lower 32 bits, and other types yield NULL. An alternative for many databases is available here: > [@Automation Professionals' Integration Toolkit Module](https://forum.inductiveautomation.com/t/automation-professionals-integration-toolkit-module/74934/89): > > Automation Professionals is pleased to announce a new release of the Integration Toolkit module. This release adds one new scripting function, [system.db.runPrepInsert()](https://www.automation-pros.com/toolkit/doc/utility.html#runprepinsert). It is very similar to IA's [system.db.runPrepUpdate()](https://docs.inductiveautomation.com/display/DOC81/system.db.runPrepUpdate), intepreting the query, args, database, and tx arguments in the same fashion. The getKey argument is omitted as moot, as this function always retrieves generated keys. The skipAudit optional argument is unimplemented. For Ignition v8.1+: [v2.0.15.240431913](https://www.automation-pros.com/ignition/Integration%20Toolkit-2.0.15.240431913-v81.modl) Do note the docu… But note that MS SQL Server always returns a double, which only has fifty-something bits to hold integers. In your situation, I'd use a stored procedure, called via the stored procedure scripting methods, with a registered output parameter to deliver the SCOPE\_IDENTITY.