Using Knex - How to retrieve Multiple result sets

Question

I'm using MSSQL, I have a stored proc which is returning multiple result sets (1 for data and 1 for record count) when I call this stored proc with knex.raw, Knex is only returning the first result set. How can I retrieve other record count result sets using Knex + Node.js?

{ "data": [{"ID": 6344,"RequestID": 910846045,"Requester": "XXXXXXX", "Started": "09/25/2018", "Completed": "09/25/2018" } { "ID": 6345, "RequestID": 910846046, "Requester": "XXXXXXX", "Started": "09/25/2018", "Completed": "09/25/2018" } ], "RecordCnt": 2 }

Answer 1

I've just came across this very same problem migrating from using mssql to knex.

The simple answer is knex doesn't support this out the box, however, the underlying MSSQL client (tedious) does. If you are familiar with the mssql client, you can simply acquire a connection from the pool and then use it as you normally would to execute a stored procedure e.g.

const connection = await knex.client.acquireConnection();
const result = await connection.request()
  .input('parameter', value)
  .execute('[procedure_name]');
knex.client.releaseConnection(connection); // we don't have to wait for this

The last line is very important, as we are manually acquiring a connection we are responsible for releasing it back to the pool. Failure to do this will lead to pool starvation and new requests will eventually start to fail (based on the size of your pool).