SELECT SCOPE_IDENTITY() to return the last inserted ID

Question

There are a few answers about this problem, but my question is about the particular code I have.

I'm trying to get the last inserted ID of this query executing on VBA code.

Public Function Execute(cQry As excfw_dbQuery) As ADODB.Recordset    

    If pConn.State = 0 Then
        OpenConnection
    End If

    qry = "INSERT INTO [some really long query, which actually works]; SELECT SCOPE_IDENTITY()"

    On Error Resume Next
    Dim rs As ADODB.Recordset
    Set rs = New ADODB.Recordset
    rs.Open qry, pConn 'also tried with adOpenKeyset, adLockOptimistic

    'some error handling code which is not related to the issue 

    Set rs = rs.NextRecordset() 'also tried without moving onto the next recordset
    pInsertedId = rs.Fields(0).Value

    Set Execute = rs 'this is just to pass the query result for SELECT queries 

End Function

This should save the last inserted ID on the pInsertedId variable, but instead I get 0 each time I insert a row. The weird thing is, when I copy and paste the same code into the SSMS, it works.

I might just get away with inserting some unique data to some unused column of the database and querying through that.

--UPDATE--

I've just noticed that when running a SELECT query, rs object remains open until it goes out of scope. Here is a screenshot of the watch section:

on an insert statement instead, it gets closed as soon as the query gets executed:

Answer 1

You can explicitly save the results of the insert statement by using an output clause and return the results with a select:

qry =
  "declare @Ids as ( Id Int );" +
  "insert into MyTable ( Name ) " + ' Assuming   Id   is an identity column.
     "output Inserted.Id into @Ids " + 
     "values ( @Name );" +
  "select Id from @Ids;"

From the documentation for output:

INSERTED Is a column prefix that specifies the value added by the insert or update operation. Columns prefixed with INSERTED reflect the value after the UPDATE, INSERT, or MERGE statement is completed but before triggers are executed.

You can use an output clause to get any data from the rows (Note plural.), e.g. identity column values for newly inserted rows. Output can be used with insert, update, delete and merge and provides access to both before and after values in the case of update. A tool well worth having in your pocket.

Answer 2

As it turns out, the table that I'm trying to insert to has multiple triggers attached to it. So, the query which includes the SELECT SCOPE_IDENTITY(); is actually 4th query. I had to move 4 queries forward in order to get the correct scope. How I pulled that off programatically is as follows. Not very clean (and possibly not the best way to do it), but does the job for me.

I basically go ahead to next recordset until there is none left, which I detect by checking the error number 91 (Object variable or with block variable not set)

Public Function Execute(cQry As excfw_dbQuery) As ADODB.Recordset    

    If pConn.State = 0 Then
        OpenConnection
    End If

    qry = "INSERT INTO [some really long query, which actually works]; SELECT SCOPE_IDENTITY()"

    On Error Resume Next
    Dim rs As ADODB.Recordset
    Set rs = New ADODB.Recordset
    rs.Open cQry.Query, pConn, adOpenKeyset, adLockOptimistic

    'some error handling code which is not related to the issue 

    On Error Resume Next
    'begin loop
    Do
        'go to next recordset
        Set rs = rs.NextRecordset()

        'if we're getting error n. 91, it means 
        'recordsets are exhausted, hence we're getting
        'out of the loop 
        If Err.Number = 91 Then
            Err.Clear
            Exit Do
        End If

        'if we are not out of recordsets, check the 
        'result of the query. If it is bigger then zero, 
        'it means we hit the jackpot. 
        If rs.Fields(0).Value > 0 Then
            pInsertedId = rs.Fields(0).Value
        End If
    Loop
    On Error GoTo 0

End Function

Again, not the cleanest, nor the most correct way to do it, but did the trick. I'm open for any improvements or suggestions.