Return inserted key with stored procedure

Ignition
, ,
1

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

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

IF @keyExists = 0 THEN INSERT INTO TABLE OUTPUT Table.ID

If I change the procedure to

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

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?

2

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?

3

This is a stored procedure in the DB. I call it using

system.db.runQuery('EXEC mySP @param1=val')
4

Running a stored procedure via general statement is suboptimal. Run it with Ignition's dedicated stored procedure functions.

5

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.

1 Like
6

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?

7

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:

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.

1 Like