How to execute Stored Procedure

Sign in to answer this question.

• 

  Muhammad Imran Ansari1y agoPosted Jan 28, 2025, 4:19 PM

  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');

   
• 

  Manikandan Murugesan1y agoPosted Jan 28, 2025, 5:00 AM

  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 (').