How to run mysql stored procedure with output return in knex.js

Question

I have a problem with knex.js

how to call stored procedure with output in knex.js

sp string : call sp_start(1, @outmsg);

I need call that SP and select the output in return

my code:

    .get('/:headerId/recount', function(req, res, next) {

    knex.raw(
    'Call sp_start(?,?)',[req.params.headerId, @outmsg]
    )
    .then(function(result) {
       return; 
        });
  })

but it return error

user_360060 · Answer

According to how to pass in and out parameters to a mysql stored procedure and return the stored procedure result in the nodejs code

knex.transaction(trx => {
  return knex.raw(
    'Call sp_start(?,@outmsg)',
    [req.params.headerId]
  )
  .then(res => knex.select(knex.raw('@outmsg')));
})
.then(res => console.log("Got output:", res));

should work.

user_14416310 · Answer

I am using an Express server with route/service. The above answer didn't work for me.

What did work was using a multiple statement raw method like so:

ExpressService(knex, param){
  return knex.raw(`call sp_start(${param}, @outmsg); select @outmsg as outmsg;`);
};

You will also need to add multipleStatements: true to your MySQL connection.

How to run mysql stored procedure with output return in knex.js

2 Answers