Node.js - adding multiple parameters dynamically using MSSQL package

Question

I am trying to write a function that takes care of all of the queries that I execute to the database, however, I am struggling to find a way to dynamically add parameters to my request.

All NPM documentation, somewhat unhelpfully, provides non-dynamic examples of adding parameters (https://www.npmjs.com/package/mssql).

Example NPM documentation:

function runStoredProcedure() {
    return pool2.then((pool) => {
        pool.request() // or: new sql.Request(pool2)
        .input('input_parameter', sql.Int, 10)
        .output('output_parameter', sql.VarChar(50))
        .execute('procedure_name', (err, result) => {
            // ... error checks
            console.dir(result)
        })
    });
}

My implementation looks like this

// promise style:
const pool2 = new sql.ConnectionPool(config, err => {
    // ... error checks
});
 
pool2.on('error', err => {
    // ... error handler
})
 
function runStoredProcedure(res, proc, sqlParams) {
    return pool2.then((pool) => {
        pool.request() // or: new sql.Request(pool2)
        .input('input_parameter', sql.Int, 10) //
        .execute(proc, (err, recordset) => {
            // ... error checks
            res.json(recordset[0]);
        })
    });
}

Ideally, I would like to declare the pool.request() and then foreach for my parameters.

I thought this question would be useful to post as real use cases of the mssql package would look at adding parameters dynamically, in spite of the examples given in the documentation.

Answer 1

pool.request returns a request object. You don't have to use the fluent API, but can ignore the return value of each input call and just call them separately:

function runStoredProcedure(res, proc, sqlParams) {
    return pool2.then((pool) => {
        const req = pool.request();
        sqlParams.forEach(function(param) {
            req.input(param.name, param.type, param.value);
        });
        req.execute(proc, (err, recordset) => {
            // ... error checks
            res.json(recordset[0]);
        });
    });
}