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.