CODEWITHSUNDEEP
javascriptsql-serverazureazure-mobile-servicesazure-app-api
Ajay Raghav
Ajay Raghav

Reputation: 981

Azure Easy API JavaScript: How to return multiple result set from MSSQL query

I have created an Azure Easy API (on an app service that was recently migrated from a mobile service). I want to return three result sets from an SQL stored procedure. But it only returns the first result set. I have read that setting multiple attribute of query to true would allow returning multiple result sets, but I am not sure how to do it. That's what my azure API looks like:

exports.get = function(request, response) { 
    var mssql = request.service.mssql;
    var param1 = request.query.pollid;
    var param2 = request.query.userid;
    var sql = "EXEC poll.GetPollsData @pollid = ?, @userid = ?";

    mssql.query(sql, [param1, param2], {
        success: function(results) { 
                 response.send(200, results); 
            }, error: function(err) {
                response.send(400, { error: err });        
        }
    });
};

GetPollsData stored procedure returns three result sets (for polls, questions and options). But the API only shows first table on client side (in pollData below).

This is the client side JavaScript:

client = new WindowsAzure.MobileServiceClient(a, k);
client.invokeApi('getpollsdata', {
    method: 'get',
    parameters: {
        pollid: p,
        userid: u,
    }
}).done(function(results) {
    pollData = JSON.parse(results.response);
}, function(error) {
    console.log(error);
});

Upvotes: 0

Views: 607

Answers (1)

Gary Liu
Gary Liu

Reputation: 13918

Please try to use the execute() function in Azure Mobile Apps in Node.js, and add the parameter multiple to true as described at http://azure.github.io/azure-mobile-apps-node/jsdoc_data.execute.js.html#line55.

E.G.

module.exports = {
    "get": function (req, res, next) {
        var query = {
            sql:'EXEC [dbo].[SampleProcedure]',
            multiple:true
        };
        req.azureMobile.data.execute(query).then(function(results){
            console.log('results',results);
            res.json(results);
        })
    }
}

It will return an array which contains multiple result arrays.

Any further concern, please feel free to let me know.

Upvotes: 1

Related Questions