How to execute Stored Procedure

Hi Ramco,

You can use the CALL statement in HANA SQL. Below is the syntax and an example to help you:

Syntex:

CALL "." (, , ...);

Example with Date and String Parameters:
Let’s assume your stored procedure looks like this:

CREATE PROCEDURE "MySchema"."MyProcedure" 
(
    IN "StartDate" DATE,
    IN "EndDate" DATE,
    IN "SearchTerm" NVARCHAR(100)
)
LANGUAGE SQLSCRIPT
AS
BEGIN
    -- Your logic here
    SELECT * FROM "MyTable"
    WHERE "CreatedDate" BETWEEN :StartDate AND :EndDate
      AND "Description" LIKE '%' || :SearchTerm || '%';
END;

Execution/Call:

CALL "MySchema"."MyProcedure" ('2025-01-01', '2025-01-28', 'TestString');

To execute a stored procedure in SAP B1 HANA with date and string parameters, you can use SQL commands through the SAP HANA database client (HANA Studio, HANA Cockpit, or SQL Console) or via SAP B1 SDK tools like the DI API or Service Layer.

Using SQL Console

Here’s the general syntax for executing a stored procedure with parameters:

CALL ""."" (, , ...);

Example Stored Procedure Execution:

Assume you have a stored procedure named usp_GetOrders with the following parameters:

• @StartDate (DATE)
• @EndDate (DATE)
• @CustomerCode (NVARCHAR or STRING)

Here’s how you would call the stored procedure:

CALL "schema_name"."usp_GetOrders" ('2025-01-01', '2025-01-28', 'CUST001');

Key Notes:

1. Replace "schema_name" with the actual schema where the stored procedure resides (default is usually dbo for B1 systems).
2. Date format in SAP HANA is typically YYYY-MM-DD.
3. String values should always be enclosed in single quotes (').