XS Callling Stored Proc with multiple values in input param

[SergioG_TX]

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!!