How to run a sql query in Tedious supplying multiple values for the sql in operator?

Question

The code below works if I supply only one parameter. If I select Germany and Mexico from the dropdown nothing is returned by the query despite data existing in the table

The code on the front-end. Sending the parameters via AJAX jQuery .post

$('select').selectpicker();
var field1 = $('#field1').val();
$.post('data.js', {
    field1: field1
})

The app.js server side:

app.post('/data.js', function(req, res) {

    var thequery1 = `SELECT top 10 country
                        ,[ Sales] sales 
                        ,[Units Sold] as sold
                        ,FORMAT( Date, 'dd/MM/yyyy', 'en-US' ) thedate
                     FROM easternsun.dbo.financial
                     where 1 = 1`
    if (req.body.field1) {
        thequery1 = thequery1 + ' and country in (@field1)'
    }
});
Promise.all([queryTablewithPararams(thequery1, req.body.field1)])
        .then(
            data => res.json(data)

        );

function queryTablewithPararams(thequery1, field1) {
    return new Promise(function(resolve, reject) {
        var con = new msSqlConnecter.msSqlConnecter(config);
        con.connect().then(function() {
            new con.Request(thequery1)
                .addParam("field1", TYPES.VarChar, field1 )
                .onComplate(function(count, datas) {
                    resolve(datas);
                }).onError(function(err) {
                    console.log(err);
                }).Run();
        }).catch(function(ex) {
            console.log(ex);
        });
    });
}

My HTML code for the dropdown:

<select id="field1" class="selectpicker form-control" multiple >
    <option value="Canada">Canada</option>
    <option value="Germany">Germany</option>
    <option value="France">France</option>
    <option value="Mexico">Mexico</option>
</select>

I'm convinced I need to add something to this line but I don't know what.

.addParam("field1", TYPES.VarChar, field1 )

Answer 1

If you select multiple values, req.body.field1 will be an array, so we'll need to change the code a little. We'll create an array of input parameters, fields, then pass it to our query function. The where in clause should then work correctly:

We also need to change the query function to accept a variable number of parameters.

I don't believe you need to change your client side code at all.

app.post('/data.js', function(req, res) {
    var thequery1 = `SELECT top 10 country
                        ,[Sales] sales 
                        ,[Units Sold] as sold
                        ,FORMAT( Date, 'dd/MM/yyyy', 'en-US' ) thedate
                    FROM easternsun.dbo.financial
                    where 1 = 1`

    // Create an array containing all input parameters
    let fields = [];
    if (req.body.field1) {
        fields = Array.isArray(req.body.field1) ? req.body.field1: [req.body.field1];
        const fieldSql = fields.map((v,index) => `@field${index}`).join(",")
        thequery1 = thequery1 + ` and country in (${fieldSql})`
    }

    Promise.all([queryTablewithPararams(thequery1, ...fields)])
    .then(data => res.json(data));
});

// We need to change this function to accept multiple parameters.
function queryTablewithPararams(thequery1, ...params) {
    return new Promise(function(resolve, reject) {
        var con = new msSqlConnecter.msSqlConnecter(config);
        con.connect().then(function() {
            let request = new con.Request(thequery1);
            // Add all the parameters
            for(let [index, param] of Object.entries(params)) {
                request = request.addParam(`field${index}`, TYPES.VarChar, param);
            }

            request.onComplate(function(count, datas) {
                    resolve(datas);
                }).onError(function(err) {
                    console.log(err);
                    reject(err);
                }).Run();
        }).catch(function(ex) {
            console.log(ex);
        });
    });
}

Answer 2

This syntax is significantly simpler and allows for filtering on more than one column. The performance on large tables will probably not be great.

thequery1 = thequery1 + ' and country in (SELECT value FROM STRING_SPLIT(@field1,\',\')) '



.addParam("field1", TYPES.VarChar, field1)