Retrieve multiple result sets in sails js

The sails-mssqlserver adapter is a wrapper of the official Microsoft SQL Server client for Node.js available here its dependecy however is not on the latest release.

Option 1:As per this official documentation of the MsSQL package, you can enable multiple recordsets in queries with the request.multiple = true command.

To enable multiple queries/recordsets in the sails-mssqlserver adapter, a hackish workaround is to open sails-mssqlserver/lib/adapter.js and edit the raw query function. Adding request.multiple = true below var request = new mssql.Request(mssqlConnect). As shown in the example below.

// Raw Query Interfacequery: function (connection, collection, query, data, cb) {  if (_.isFunction(data)) {    if (debugging) {      console.log('Data is function. A cb was passed back')    }    cb = data    data = null  }  adapter.connectConnection(connection, function __FIND__ (err, uniqId) {    if (err) {      console.error('Error inside query __FIND__', err)      return cb(err)    }    uniqId = uniqId || false    var mssqlConnect    if (!uniqId) {      mssqlConnect = connections[connection].mssqlConnection    } else {      mssqlConnect = connections[connection].mssqlConnection[uniqId]    }    var request = new mssql.Request(mssqlConnect)    // Add it here    request.multiple = true    request.query(query, function (err, recordset) {      if (err) return cb(err)      if (connections[connection] && !connections[connection].persistent) {        mssqlConnect && mssqlConnect.close()      }      cb(null, recordset)    })  })},

Now the returned recordset should contain multiple results.

Option 2:A more sustainable option for use cases where running a stored procedure which returns multiple recordsets, is to use the latest version of the official Microsoft SQL Server client for Node.js. Information on running stored procedures is available here

First install the latest package:

npm install mssql --save

In your code where you would like to run the stored procedure add a connection to the mssql database:

// require the mssql packageconst sql = require('mssql')// make a connection, you can use the values you have already stored in your adapterconst pool = new sql.ConnectionPool({    user: sails.config.connections.<yourMsSQLConnection>.user,    password: sails.config.connections.<yourMsSQLConnection>.password,    server: sails.config.connections.<yourMsSQLConnection>.server,    database: sails.config.connections.<yourMsSQLConnection>.database})// connect the pool and test for error pool.connect(err => {    // ...})// run the stored procedure using requestconst request = new sql.Request()request.execute('procedure_name', (err, result) => {    // ... error checks     console.log(result.recordsets.length) // count of recordsets returned by the procedure    console.log(result.recordsets[0].length) // count of rows contained in first recordset    console.log(result.recordset) // first recordset from result.recordsets    console.log(result.returnValue) // procedure return value    console.log(result.output) // key/value collection of output values    console.log(result.rowsAffected) // array of numbers, each number represents the number of rows affected by executed statemens     // ...})// you can close the pool usingpool.close()

In cases, where the sails-* database adapter doesn't include all the functionality you require. I find it best to create a sails Service that wraps the additional functionality. It is a really clean solution.