Skip to Content
Author's profile photo Sergio Guerrero

XS Callling Stored Proc with multiple values in input param

First of all, I was inspired on writing this article as I have been doing this type of scenario for a few years and it is a #simple, yet #powerful approach. In this scenario, I want to describe how to send multiple values into a stored procedure and also returning an array of errors.

First, assume that we need to send multiple records in as a structure, such as a list of values. in my case, I am only adding one column in the array, however, the array may contain more than one property (see output structure for more than 1 column).

My input array looks as follows (and I pass that as n input param to the stored proc):

var someArray = [ { paramValue: value},..,{paramValue: someValueN} ];

my output array will look like (returned as an output param from the stored proc):

var outputError = [ { Code: “code”, Message: “someMessage”},.,{ Code: “code2”, Message: “someMessage2”} ];

in order to accomplish this, I created types in my CDS file as:


my stored proc looks like this:

1) look at the input param data type – it is the same as the type declared in the CDS file

2) similar to 1 – the output param data type is showed in the CDS file

I  purposely left some comments in this stored proc to showcase that:

a) multiple input/output parameters can be provided

b) the output parameter may be built based on some rules (in my case to return errors)

c) the input parameter may be used as a JOIN to return a subset of records based on a business rule, perform a union, etc.


now from my XSJS code, I need to invoke the stored proc anc pass the input param with multiple values and get the output based on my variable name:


I hope this is easy to follow and again – this was a personal experience example to demonstrate the use of invoking stored procedures from XSJS (SP09 and later) and passing multiple values into an input parameter as well as being able to read the result set of an output parameter based on the variable name.

please share your experiences and also provide feedback in case you have similar scenarios

thanks for your time in reading this post!!

Assigned Tags

      You must be Logged on to comment or reply to a post.
      Author's profile photo Sergio Guerrero
      Sergio Guerrero
      Blog Post Author

      I should add - this scenario was used on a XSJS REST web service which contains business logic and custom errors - hence I couldn't use odata due to business rules validations, etc.

      Author's profile photo Jay Prakash Singh
      Jay Prakash Singh

      Hi All,


      I am facing issue described in the blog

      can anyone take a look please .




      Author's profile photo Sergio Guerrero
      Sergio Guerrero
      Blog Post Author

      did you try the method described in this blog? i know you are trying to use odata.. but have you tried this way?

      Author's profile photo Jay Prakash Singh
      Jay Prakash Singh

      Hi Sergio,


      Actually i am deploying my service into the gateway there i have created my service with the help of OCI, It only exposes one service at a time it means if i will create xsjs service i have to create another service ,which is not good, that is why i am trying to accommodate in xsodata.

      Is this possible to perform batch in xsodata or i need to use xsjs only?



      Author's profile photo Sergio Guerrero
      Sergio Guerrero
      Blog Post Author

      I am not familiar w the services in the gateway, however, technically, yes you should be able to do a batch operation via odata.

      Author's profile photo Jay Prakash Singh
      Jay Prakash Singh

      Hi Sergio Guerrero ,


      Can you give me code for xsjs my email id is also can you check below code if it is correct.


      right now i am getting error JSON.parse: unexpected character at line 1 column 1 of the JSON data


      actually i am not able to debug xsjs with some technical reason so i am trying to solve using sapui5 request only and when i call service from sapui5 in response i am getting this error.


      xsjs code :


      function getInputAsInTT(listOfValues){
      var i=0;
      var inTT = [];
      paramValue : listOfValues[i]
      return inTT;

      function getTxtData(request) {
      /*var connection = $.db.getConnection();*/

      var connection = $.hdb.getConnection();
      var output = {errors:[],otherOutputs:[]};

      try {

      var dataEntryQuery = connection.loadProcedure("schema","path.procedure::InsertValues");

      var inVals = getInputAsInTT(request);
      var result = dataEntryQuery(inVals,[]);
      output.errors = result.OUT.ERRORS;

      } catch(ex) {
      output.errors = ['there is error' + ex.message];
      return output;

      function doGet() {
      try {
      var aCmd = $.request.parameters.get('newEntries');
      var ajson = JSON.stringify(aCmd) ;
      var obj = JSON.parse(ajson);
      var newEntries =;
      $.response.contentType = "application/json";
      } catch (err) {

      $.response.returnCode = 200;


      sapui5 code :


      headers: myHeadersend,
      url: url2,
      type: "POST",
      data: {
      newEntries: JSON.stringify(oEntry)
      processData: false,
      async: false,
      timeout: 1000000,
      error: function(jqXHR, textStatus, errorThrown) {

      success: function(data, textStatus, jqXHR) {




      oEntry will be a object with attribute and if it work i will pass whole object array to insert batch operation.