Getting Started with SAP BTP HANA Cloud Stored Procedures: A Beginner's Guide with Real-Life Example
- Raven Infotech
- Jun 16
- 3 min read
If you're building on SAP BTP and working with HANA Cloud, you've probably heard about stored procedures. But for many developers—especially those coming from JavaScript or Node.js backgrounds—writing SQLScript can feel like entering a whole new world.
Don’t worry. This blog is for you.
In this guide, we’ll go from “What is a stored procedure?” to “How do I write and call one from my Node.js CAPM backend?” We’ll also walk through a real-world example that’s practical, not academic fluff.
Let’s break it down step by step.
What Is a Stored Procedure?
A stored procedure is a reusable block of SQL logic that is saved in your HANA database and executed on demand.
Think of it like a JavaScript function—but for your database.
It can take inputs (parameters)
It can do processing (filters, joins, loops, calculations)
It can return results (either as output params or result sets)
And the best part: it runs directly on the database, reducing data movement and improving performance
Why Use Stored Procedures in SAP BTP HANA Cloud?
To encapsulate business logic inside the DB layer
To avoid writing the same query logic multiple times
To perform complex calculations or batch processing
To boost performance when dealing with large datasets
Basic SQLScript Concepts (Beginner’s Toolbox)
Before we dive in, here are some SQLScript basics you'll use often:
Concept | Syntax Example | Description |
Declare variable | DECLARE lv_count INT; | Local variable inside the procedure |
Assign value | lv_count := 10; | Set value to a variable |
Control flow | IF...ELSE, FOR, WHILE | Loops and conditionals |
SELECT INTO | SELECT COUNT(*) INTO lv_count FROM my_table; | Assign query result into a variable |
RETURN | RETURN; | Ends procedure |
Step-by-Step: Write Your First Medium-Complex Stored Procedure
Real-Life Use Case: Generate Customer Invoice Summary
Let’s say we have two tables:
CUSTOMERS(CUSTOMER_ID, NAME)
INVOICES(INVOICE_ID, CUSTOMER_ID, AMOUNT, STATUS)
We want to create a procedure that:
Accepts a customer_id
Calculates total invoice amount
Counts how many are PAID and how many are PENDING
Returns all this info in a result set
Step 1: Create the Procedure
Open your SAP HANA Database Explorer and run the following SQL:
CREATE OR REPLACE PROCEDURE get_customer_invoice_summary (
IN i_customer_id NVARCHAR(10),
OUT o_result TABLE (
total_amount DECIMAL(18,2),
total_paid INT,
total_pending INT
)
)
LANGUAGE SQLSCRIPT
AS
BEGIN
DECLARE lv_paid INT;
DECLARE lv_pending INT;
DECLARE lv_total DECIMAL(18,2);
SELECT SUM(AMOUNT) INTO lv_total
FROM INVOICES
WHERE CUSTOMER_ID = :i_customer_id;
SELECT COUNT(*) INTO lv_paid
FROM INVOICES
WHERE CUSTOMER_ID = :i_customer_id AND STATUS = 'PAID';
SELECT COUNT(*) INTO lv_pending
FROM INVOICES
WHERE CUSTOMER_ID = :i_customer_id AND STATUS = 'PENDING';
o_result = SELECT :lv_total AS total_amount,
:lv_paid AS total_paid,
:lv_pending AS total_pending;
END;
Step 2: Test the Procedure Run this to test in your SQL console:
CALL get_customer_invoice_summary('CUST001', ?);
The output should show total invoice amount, paid count, and pending count for the given customer.
Step 3: Call It from a CAPM Node.js Backend
Let’s say you have a Node.js CAPM service and you want to expose this data via an API.
a) Install HANA Client
npm install @sap/hana-client
b) Add the logic in your CAPM handler (e.g., inside srv/invoice-service.js):
const hanaClient = require('@sap/hana-client');
module.exports = srv => {
srv.on('getInvoiceSummary', async (req) => {
const customerId = req.data.customerId;
const db = await cds.connect.to('db');
const conn = await db.acquire();
const stmt = conn.prepare(`CALL get_customer_invoice_summary(?, ?)`);
const output = await new Promise((resolve, reject) => {
stmt.exec([customerId], (err, result) => {
if (err) return reject(err);
resolve(result);
});
});
return output;
});
};
c) Expose this in your invoice-service.cds:
service InvoiceService {
function getInvoiceSummary(customerId: String): {
total_amount: Decimal(18,2),
total_paid: Integer,
total_pending: Integer
};
}
Now you can call /invoice-service/getInvoiceSummary?customerId='CUST001' and get real data!
Final Tips for Beginners
Start simple, then layer in complexity.
Always test your procedure in HANA Explorer first before calling from app.
Use ? placeholder for OUT parameters if you're not handling them explicitly.
Keep your logic readable—SQLScript is powerful but easy to overcomplicate.
Wrapping Up
Stored procedures can be your superpower when used wisely in SAP BTP HANA Cloud.
They let you encapsulate business logic, boost performance, and keep your CAPM service layers clean. In this blog, we walked through SQLScript basics, wrote a real-world procedure, and called it from a Node.js backend.
Give it a try in your own project—and once you're confident, explore more advanced topics like error handling, cursors, and parameterized result sets.
If this helped you get started, let me know—or share what procedure you built!
Comments