New post
Question
Yakov Berger · Nov 21, 2021

Getting SCOPE_IDENTITY() from SQL SERVER

#InterSystems IRIS

Hi,

In a Business Operation i have the following code.

I need the identity of the latest row inserted in a table in MSSQL.

i Have the following code:

---------------------------------------------------------

set insertmaster= "INSERT INTO dbo.dataMasterArchive(deviceToPatientLogID) values (?)"

set tSC = ..Adapter.ExecuteUpdate(.nrows1,insertmaster,pRequest.deviceToPatientLogID)                                
$$$ThrowOnError(tSC)

set sql ="SELECT SCOPE_IDENTITY()"
set tSC = ..Adapter.ExecuteQuery(.tResult,sql)
$$$ThrowOnError(tSC)

set maxID = tResult.Get(1)

------------------------------------------------------------

the result of maxID  after the above is NULL.

what am i doing wrong?.

 

Thanks

Product version: IRIS 2019.1
0
0 217
Discussion (2)1
Log in or sign up to continue
Tani Frankel · Dec 29, 2021

Working with Yakov on this, we saw that for SCOPE_IDENTITY() to work on the SQL Serve side it needs to be in the same "scope" of the INSERT (for example in the same Stored Procedure), see here for reference.

So what Yakov ended up doing was encapsulating the INSERT and SELECT SCOPE_IDENTITY() into a Stored Procedure which returns the newly inserted Row ID, and call the SP via the Adapter, thus inserting the new record and getting back the new ID.

0 0