Skip to Content

Create HANA Stored Procedure and Expose as CAP Service Function (SAP HANA Cloud)

Further combine SAP HANA Cloud native artifacts with the SAP Cloud Application Programming Model (CAP), and expose SQLScript procedures as service functions.
You will learn
  • How to create and deploy an SAP HANA Cloud SQLScript stored procedure
  • How to create a service function implemented via an SAP HANA SQLScript stored procedure
  • How to use a CAP service exit to implement the service function
jung-thomasThomas JungNovember 29, 2022
Created by
March 1, 2021


  • This tutorial is designed for SAP HANA Cloud. It is not designed for SAP HANA on premise or SAP HANA, express edition.
  • You have created database artifacts and loaded data as explained in the previous tutorial.

Video Version

Video tutorial version:

  • Step 1
    1. In the /db/src folder create a new database artifact named sleep of type hdbprocedure

      Create Procedure
    2. This is a very simple SAP HANA Stored Procedure that calls the built-in SYNC library to put processing to sleep for 10 seconds. It’s a nice tool to be able to test the impact of long running queries without actually putting unnecessary load on the system.

      PROCEDURE "sleep" ( )
      call SyncLib:SLEEP_SECONDS(10);
    3. Deploy the to the database again using the SAP HANA Projects view.

    4. Check the deployment log to make sure everything was successfully created in the database.

      Check Log
    5. Open the HDI Container in the Database Explorer

      Open Database Explorer
    6. This new Procedure is there now and can be tested.

      Test from Database Explorer
    7. There isn’t much output from the execution, but the 10 second wait makes testing quite easy.

      10 second wait
  • Step 2
    1. Now we want to add this Procedure to the CAP service as a function. Edit /srv/interaction_srv.cds.

      Add: function sleep() returns Boolean; to the service definition.

      This will expose an OData Function as part of the service interface.

      Add Function
    2. Just adding the function to the service definition doesn’t implement it. Unlike tables and views which are implemented by the built-in handler classes of CAP, we need to use a service handler exit to implement this ourselves. In order to do this create a file named interaction_srv.js in the /srv folder. The name must match interaction_srv.cds just with the different file extension for JavaScript. The matching naming tells CAP that you want to use this file to write custom exit handlers for the services defined in interaction_srv.cds.

      Create JavaScript Exit Handler
    3. In interaction_srv.js we will implement the call to the Stored Procedure. This logic will implement the exit handler for this function which in turn uses the standard hdb Node.js module to call the Stored Procedure from SAP HANA Cloud. Save the file.

      Call Stored Procedure
      const cds = require('@sap/cds')
      module.exports = cds.service.impl(function () {
          this.on('sleep', async () => {
          try {
              let dbQuery = ' Call "sleep"( )'
              let result = await, { })
              return true
          } catch (error) {
              return false
    4. From the terminal return to the root of the project and issue the command: cds build

      cds build
    5. From the console in the project root hopefully you still have the cds watch ... running. Otherwise start it again with cds watch --profile hybrid to start the CAP service layer for testing. If you have performed the tutorial SAP HANA Cloud, Add User Authentication to Your Application, remember you must also run the application router to test your service with authentication.

    6. The CAP preview UI doesn’t list functions or actions, however. Manually add /catalog/sleep() to the end of the URL. If it works correctly it should take 10 seconds to respond since the procedure is running a sleep operation for that long.

      Test Service

    You’ve now added an OData function to your service layer which in turn is implemented as an SAP HANA Stored Procedure

Back to top