CODEWITHSUNDEEP
node.jssql-serverdatabase
LemusThelroy
LemusThelroy

Reputation: 303

Node.js - adding multiple parameters dynamically using MSSQL package

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.

Upvotes: 3

Views: 3960

Answers (2)

Sandip Patel
Sandip Patel

Reputation: 1

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

Upvotes: 0

Mureinik
Mureinik

Reputation: 312106

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

Upvotes: 7

Related Questions