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.

LemusThelroy · Accepted Answer · 2019-03-05 15:00:24Z

7

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]);
        });
    });
}

edited Mar 5, 2019 at 15:00

LemusThelroy

3031 gold badge6 silver badges15 bronze badges

answered Mar 5, 2019 at 9:54

Mureinik

316k54 gold badges399 silver badges405 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

LemusThelroy Over a year ago

Superb - for future reference I should have looked more closely at the return type of pool.request() for clues on how to do this. Much appreciated.

Sandip Patel · Accepted Answer · 2020-04-02 11:37:25Z

0

// create parameter json
    let sqlParams = [{ "name": "ParamName1", "type": sql.Date, "value": obj.ParamName1 },
                     { "name": "ParamName2", "type": sql.Int, "value": obj.ParamName2 }];

// Call Function
    let result = await getDbResult(sqlParams, 'Procedure name ');




// Dynamic function of call Stored Procedure

exports.getDbResult = async (sqlParams, SpName) => {
    try {
        let pool = await sql.connect(dbconfig)
        const result1 = await pool.request();
        sqlParams.forEach(function (param) {
            result1.input(param.name, param.type, param.value);
        });
        return await result1.execute(SpName);
    } catch (err) {
        console.log(err);
        }
}

edited Apr 2, 2020 at 11:37

answered Apr 2, 2020 at 11:32

Sandip Patel

12 bronze badges

Collectives™ on Stack Overflow

Node.js - adding multiple parameters dynamically using MSSQL package

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related