Node.js and MSSQL stored procedures

Question

What would be the suggested way (if even possible) to call MSSQL SP from Node.js. The documentation https://www.npmjs.org/package/mssql is great but there is no mention of SP (stored procedure) anywhere.

Answer 1

The linked document does actually mention stored procedures:

var request = new sql.Request(connection);
    request.input('input_parameter', sql.Int, 10);
    request.output('output_parameter', sql.VarChar(50));
    request.execute('procedure_name', function(err, recordsets, returnValue) {
        // ... error checks

        console.dir(recordsets);
    });

Not sure it's wise to answer this question, but it might be valueable for future readers/googlers.

Answer 2

It's getting better with ES6/7 additions to JS. This is how you can do it with async/await:

async function getDataFromProcedure(dbConfig, procedureName) {
  try {
    await sql.connect(dbConfig);
    const request = new sql.Request();
    recordsets = await request.execute(procedureName);
    return recordsets[0];
  } catch (error) {
    // handle error here
  }
};

Answer 3

Alternate async/await syntax. I like the .then() format.

return await this.sqlConnectionPool.connect().then(async (pool) => {
  return await pool
    .request()
    .input("UserID", sql.Int, id)
    .execute("spADF_User_Get")
    .then((result) => {
      if (result.recordset && result.recordset.length === 1) {
        return result.recordset[0];
      } else {
        //Something bad happened
      }
    });
});