Stephen Last Stephen Last - 7 months ago 85
SQL Question

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

I'm using

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

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.