node-mssql Transaction insert - Returning the inserted id..?

Question

I'm using node-mssql 3.2.0 and I need to INSERT INTO a table and return the id of the inserted record.

I can successfully use sql.Transaction() to insert data, but the only parameters given to callbacks (request.query() and transaction.commit()) are:

const request = new sql.Request();
request.query('...', (err, recordset, affected) => {});

const transaction = new sql.Transaction();
transaction.commit((err) => {});

So recordset is undefined for INSERT, UPDATE and DELETE statements, and affected is the number of rows affected, in my case 1.

Does anyone know a good way to obtain an inserted records id (just a primary key id) after a transaction.commit() using node-mssql..?

Answer 1

Instead of just doing an INSERT INTO... statement, you can add a SELECT... statement as well:

INSERT INTO table (...) VALUES (...); SELECT SCOPE_IDENTITY() AS id;

The SCOPE_IDENTITY() function returns the inserted identity column, which means recordset now contains the id:

const request = new sql.Request();
request.query('...', (err, recordset, affected) => {});

I don't think request.multiple = true; is required, because although this includes multiple statements, only one of them is a SELECT... and so returns.

So the answer was SQL related and is not specific to node-mssql.

Answer 2

I know this question has accepted answer. I made the following way:

    let pool = await sql.connect(config);
    let insertItem = await pool.request()
    .input('ItemId',sql.NVarChar, 'itemId1234')
    .input('ItemDesc',sql.NVarChar, 'nice item')
    .query("insert into itemTable (Id, ItemId,ItemDesc) OUTPUT INSERTED.ID 
    values (NEWID(), @ItemId, @ItemDesc);
    var insertedItemId = insertItem.recordset[0].ID

This adds unique identifier to data that is saved to db (if table is created so)

create table itemTable(

       Id UNIQUEIDENTIFIER primary key default NEWID(),
       ItemId nvarchar(25),
       ItemDesc nvarchar(25)
)