41 · Stored Procedures

Create .hdbprocedure artifacts and call stored procedures from CAP handlers with parameters.

Episode 41 — Stored Procedures & Native SQL

What you'll learn

• When to use native SQL / stored procedures vs CAP query API
• Calling HANA stored procedures from CAP handlers
• Creating procedures via HDI (.hdbprocedure)
• Safe parameter passing (no SQL injection)
• Transactions with stored procedures

Why this matters

90 % of logic belongs in CDS handlers. But some operations — complex ETL, hierarchical data processing, bulk data moves — are 100x faster as HANA stored procedures. Knowing how to call them from CAP without breaking the transaction model is essential.

When native SQL makes sense

Simple native SQL from a handler

const db = await cds.connect.to('db')

this.on('calculateRevenue', async (req) => {
  const { productId, year } = req.data

  // Parameterized query — safe from SQL injection
  const result = await db.run(
    `SELECT SUM(oi.QUANTITY * oi.PRICE) AS revenue
     FROM ORDER_ITEMS oi
     JOIN ORDERS o ON oi.ORDER_ID = o.ID
     JOIN PRODUCTS p ON oi.PRODUCT_ID = p.ID
     WHERE p.ID = ? AND YEAR(o.CREATED_AT) = ?`,
    [productId, year]
  )

  return { revenue: result[0]?.REVENUE ?? 0 }
})

Creating a stored procedure via HDI

-- gen/db/src/proc/ReassignOrders.hdbprocedure
PROCEDURE "ReassignOrders" (
  IN  p_old_assignee NVARCHAR(100),
  IN  p_new_assignee NVARCHAR(100),
  OUT p_count        INTEGER
)
LANGUAGE SQLSCRIPT AS
BEGIN
  UPDATE ORDERS
    SET ASSIGNEE = :p_new_assignee
  WHERE ASSIGNEE = :p_old_assignee
    AND STATUS IN ('DRAFT','SUBMITTED');

  SELECT COUNT(*) INTO p_count
  FROM ORDERS
  WHERE ASSIGNEE = :p_new_assignee;
END;

Calling the procedure from CAP

this.on('reassignOrders', async (req) => {
  const { oldAssignee, newAssignee } = req.data
  const db = await cds.connect.to('db')

  // HANA node.js client call
  const result = await db.run(
    `CALL "ReassignOrders"(?, ?, ?)`,
    [oldAssignee, newAssignee, null]   // null = OUT parameter placeholder
  )

  return { count: result.p_count }
})

Transactions with stored procedures

CAP automatically wraps handlers in a transaction. Procedures called via db.run() join that transaction:

this.on('bulkProcess', async (req) => {
  const db = await cds.connect.to('db')

  // All three calls are in the same transaction
  await db.run(`UPDATE ORDERS SET STATUS = 'PROCESSING' WHERE ...`)
  await db.run(`CALL "ProcessBatch"(?, ?)`, [req.data.batchId, null])
  await db.run(`INSERT INTO AUDIT_LOG ...`)

  // Transaction commits when the handler returns successfully
  // Rolls back if the handler throws
})

Avoiding SQL injection

// WRONG — vulnerable to SQL injection
const title = req.data.title
await db.run(`SELECT * FROM BOOKS WHERE TITLE LIKE '%${title}%'`)

// CORRECT — parameterized
await db.run(`SELECT * FROM BOOKS WHERE TITLE LIKE ?`, [`%${title}%`])

// ALSO CORRECT — CDS query API (always safe)
await SELECT.from('Books').where(`title like`, `%${req.data.title}%`)

Bulk operations via native SQL

// Mass update without loading into JS (pure SQL)
this.on('closeExpiredOrders', async (req) => {
  const db = await cds.connect.to('db')
  const cutoff = new Date(Date.now() - 30 * 24 * 60 * 60 * 1000).toISOString()

  await db.run(
    `UPDATE ORDERS SET STATUS = 'EXPIRED'
     WHERE STATUS = 'SUBMITTED' AND CREATED_AT < ?`,
    [cutoff]
  )
})

Inspecting native query results

const rows = await db.run(`SELECT * FROM BOOKS LIMIT 5`)
// rows is an array of plain objects with UPPER_CASE column names (HANA default)
// { ID: '...', TITLE: '...', PRICE: 29.99 }

// Normalise column names if needed
const books = rows.map(r => ({
  id:    r.ID,
  title: r.TITLE,
  price: r.PRICE
}))

Hands-on exercise

1. Write a stored procedure ArchiveOldOrders(p_days INTEGER, OUT p_count INTEGER) in an .hdbprocedure file
2. Call it from a CAP action archiveOrders(days: Integer) returns { count: Integer }
3. Add a bulk UPDATE via native SQL that sets all archived orders' status
4. Verify the transaction: make the procedure throw midway and confirm the UPDATE is rolled back

Checkpoint ✓

• [ ] Always use parameterized queries (? placeholders) — never string interpolation
• [ ] Procedures created via .hdbprocedure HDI artifact are deployed by cds deploy
• [ ] db.run('CALL proc(?, ?)', [in1, null]) calls a procedure; OUT params come back as named properties
• [ ] Native SQL inside a handler automatically joins the request's transaction