Skip to Content
Technical Articles
Author's profile photo Sebastian Van Syckel

Calling a procedure with table input on HANA in a CAP Node.js handler

During a customer’s migration from XSA to CAP on BTP, the following question came up: How can a stored procedure with table input be called in a custom handler? As this is certainly not the only customer facing this challenge, I wanted to share the results of my research (without claiming it being the best solution).

On XSA, this was typically done using @sap/hdbext, as its loadProcedure function provides this functionality out of the box. However, using @sap/hdbext includes handling credentials, creating connections, managing transactions, etc. And this is something CAP already does for you. Additional to the development overhead, you also want to join the same transaction that is used by all other database interactions to maintain atomicity. That is, either all operations are applied or none are. Hence, we need to do what @sap/hdbext does behind the scenes, which is creating so-called local temporary tables, populating the tables with the respective data, and then call the respective procedure.

This blog post shows how to do so based on our well-known bookshop example. We’ll make use of a stored procedure with a table input that returns the best-selling book per author, filtered by a list of author IDs that is provided by the client.


Data Model

We start with a basic data model including Books and Authors, where each book can have one author.


namespace my.bookshop;

entity Books {
  key ID : Integer;
  title  : String;
  stock  : Integer;
  author : Association to Authors;

entity Authors {
  key ID : Integer;
  name   : String;
  books  : Association to many Books on = $self;


Stored Procedure

The stored procedure bestseller_by_author uses a window function to determine the book per author with the least items in stock (assuming this means the most sales), where the author’s ID must be included in the IN TABLE author, which shall be provided by the client.


PROCEDURE bestseller_by_author (
  IN author TABLE ( ID INTEGER ),
  OUT bestseller TABLE ( ID INTEGER )
  bestseller =
    FROM (
      FROM my_bookshop_Books
      WHERE author_ID in (SELECT ID FROM :author)
    WHERE ROW_NUM = 1;


Service Model

We define a CatalogService that exposes a projection on Books as well as a custom action getBestsellerByAuthor that can be called with an array of integers as filter input for the stored procedure from above.


using my.bookshop as my from '../db/data-model';

service CatalogService {
  @readonly entity Books as projection on my.Books;

  action getBestsellerByAuthor(authors: many Integer) returns many Books;


Custom Handler

After all this ground work, we have now reached the interesting part: the custom handler calling the stored procedure with table input. As mentioned in the introduction, this is only possible using so-called local temporary tables (see CREATE TABLE Statement in HANA documentation).

Hence, the handler works as follows:

  1. Create the local temporary table based on the stored procedure (i.e., with a single column ID of type INTEGER) with a random name to prevent any table already exists errors. Further, the name of a local temporary table must start with #. Hence, we generate a name following pattern #ltt_<random chars>.
  2. Once the table was created, insert the passed data using placeholder syntax to avoid SQL injection.
  3. Call the stored procedure while referencing the newly created local temporary table. Please note that this currently must be done in uppercase in order for the runtime to match in- and output parameters (for which a metadata lookup is required).
  4. Drop the local temporary table for housekeeping purposes. This is not necessarily mandatory, as we use random names and local temporary tables only exist in the confines of a session. However, sessions may be reused (fetch idling connection from pool), so it should be considered good practice.
  5. Finally, we select the books based on the book IDs we got from the stored procedure, and return the full entities to the client.

As you may or may not have noticed, we simply do await…) for all database interactions (plus the final await SELECT). We can do so due to CAP’s automatic transaction management. If not explicitly requested via APIs such as cds.tx(tx => {…}), CAP executes all statements inside a custom handler in a so-called nested transaction, which is committed or rolled back together with the respective root transaction that defines a unit of work.


const cds = require('@sap/cds')
const LOG = cds.log('cat-service')

module.exports = function () {
  const { Books } = this.entities

  this.on('getBestsellerByAuthor', async function (req) {
    const ltt = `#ltt_${cds.utils.uuid().replace(/-/g, '')}` //> random name
    await`INSERT INTO ${ltt} VALUES (?)`, => [a]))
    const query = `CALL BESTSELLER_BY_AUTHOR(AUTHOR => ${ltt}, BESTSELLER => ?)`
    const { BESTSELLER } = await
    await`DROP TABLE ${ltt}`) //> cleanup
    return await SELECT.from(Books).where('ID in', => b.ID))



Finally, let’s test our custom action that calls the stored procedure with the following request:

POST http://localhost:4004/odata/v4/catalog/getBestsellerByAuthor
Content-Type: application/json

  "authors": [150, 42]

After some time (this is a costly implementation!), the server answers with the following response:

HTTP/1.1 200 OK
Content-Type: application/json;odata.metadata=minimal

  "@odata.context": "$metadata#Books",
  "value": [
      "ID": 251,
      "title": "The Raven",
      "stock": 100,
      "author_ID": 150
      "ID": 261,
      "title": "The Hitchhiker's Guide to the Galaxy",
      "stock": 100,
      "author_ID": 42


Now you’ll either have to trust me that there were multiple books per author in the initial data, or you’ll have to try it yourself! 😉

Assigned Tags

      You must be Logged on to comment or reply to a post.
      Author's profile photo Martin Stenzig
      Martin Stenzig

      Great example! Thx!

      Author's profile photo Sebastian Van Syckel
      Sebastian Van Syckel
      Blog Post Author

      Thanks, Martin. 🙂

      Author's profile photo Tiago Almeida
      Tiago Almeida

      Interesting approach. Thanks for sharing.


      I have solved this in the past by having a normal persistent table created.

      Before calling the procedure Cap writes into this table with the records tagged in some way and then passes the tag (a string) to the procedure which selects from the table.

      A simpler approach which also would work for this example is to pass the IDs concatenated as one big string separated by comma for example.

      In sqlscript there's a library function to split a string by a character directly into a local table so would be 2 lines of code on that side.

      Author's profile photo Sebastian Van Syckel
      Sebastian Van Syckel
      Blog Post Author

      Thanks for the feedback. Using a persistent table could be a performance boost!

      Author's profile photo Arley Triana Morin
      Arley Triana Morin

      Could CAP provide similar functionality to the @sap/hdbext package, automating the creation of local temporary tables, populating them with the required data, and subsequently invoking the relevant procedure? This would significantly streamline the development process.

      Author's profile photo Sebastian Van Syckel
      Sebastian Van Syckel
      Blog Post Author

      We should look into this with the new HANA service. Passing an existing connection to @sap/hdbext is also an option, as I recently learned. However, not sure how high the priority is as it is an edge case.