not able to call procedure within procedure in HANA Studio

Question

I am trying to call a procedure I made earlier in new procedure. I am getting error in same line no matter what I try. My original line was :

CALL "PAYROLLDBTEST"."ABS_GetEmployeeHistoryDetail"(:EmpID)

on this I got error "invalid name of function or procedure: ABS_GETEMPLOYEEHISTORYDETAILS: "

then I tried CALL "PAYROLLDBTEST/ABS_GetEmployeeHistoryDetail"(EmpID) on this I got error "sql syntax error: incorrect syntax near "(":"

So please let me know whats wrong.

EDIT: Heres the whole procedure :

CREATE PROCEDURE "PAYROLLDBTEST".GetEmploymentHistoryFunction 
(IN EmpID integer, IN StartDate timestamp, IN EndDate timestamp,OUT RETURNVALUE   NVARCHAR) 
LANGUAGE SQLSCRIPT 
AS 

BEGIN 
SELECT *, DAYS_BETWEEN("FromDate", "ToDate") + 1 AS "DaysCount" 
 FROM (SELECT "Code", "Name", "U_LineID", "U_empID", "U_Status", 
        CASE 
            WHEN ("ToDate" < :StartDate) THEN NULL 
            WHEN ("FromDate" > :EndDate) THEN NULL 
            WHEN ("FromDate" < :StartDate AND ("ToDate" BETWEEN :StartDate AND :EndDate)) THEN :StartDate 
            WHEN ("FromDate" < :StartDate AND "ToDate" > :EndDate) THEN :StartDate 
            WHEN (("FromDate" BETWEEN :StartDate AND :EndDate) AND
             ("ToDate" BETWEEN :StartDate AND :EndDate)) THEN "FromDate" 
            WHEN (("FromDate" BETWEEN :StartDate AND :EndDate) AND "ToDate" > :EndDate) THEN "FromDate" 
            WHEN ("ToDate" IS NULL AND "FromDate" < :StartDate) THEN :StartDate 
            WHEN ("ToDate" IS NULL AND ("FromDate" BETWEEN :StartDate AND :EndDate)) THEN "FromDate" 
        END AS "FromDate", 
        CASE 
            WHEN ("ToDate" < :StartDate) THEN NULL 
            WHEN ("FromDate" > :EndDate) THEN NULL 
            WHEN ("FromDate" < :StartDate AND ("ToDate" BETWEEN :StartDate AND :EndDate)) THEN "ToDate" 
            WHEN (("FromDate" BETWEEN :StartDate AND :EndDate) AND
             ("ToDate" BETWEEN :StartDate AND :EndDate)) THEN "ToDate" 
            WHEN ("FromDate" < :StartDate AND "ToDate" > :EndDate) THEN :EndDate 
            WHEN (("FromDate" BETWEEN :StartDate AND :EndDate) AND "ToDate" > :EndDate) THEN :EndDate 
            WHEN ("ToDate" IS NULL AND "FromDate" < :StartDate) THEN :EndDate 
            WHEN ("ToDate" IS NULL AND ("FromDate" BETWEEN :StartDate AND :EndDate)) THEN :EndDate 
        END AS "ToDate", "U_Position", "U_Project", "U_Sponsorship" 
    FROM (
    --select * from ABS_GetEmployeeHistoryDetails WHERE ("EmpID" IN (:EmpID))
  --select * from "PAYROLLDBTEST"."ABS_GetEmployeeHistoryDetails" WHERE ("EmpID" IN (:EmpID))
  CALL "PAYROLLDBTEST"."ABS_GetEmployeeHistoryDetails"(:EmpID,:RETURNVALUE);

 ) InnerQuery
 ) OuterQuery
 WHERE ("FromDate" between :StartDate and :EndDate OR "ToDate" between :StartDate and    :EndDate);


END;

Thanks

Solution

First of all, make sure that your procedure is really located in the schema your are trying to call (in your case PAYROLLDBTEST).

You can check that by having a look in the Catalog in HANA Studio - open the Catalog, then your schema PAYROLLDBTEST and then the folder Procedures. Your procedure should be located in this folder. If not, try to refresh this folder. If the procedure is still not in there, it's definitely stored in another schema.

Second, be sure you call the procedure with the correct amount of parameters.

Your mentioned procedure seems to have only one parameter, EmpID, which seems to be an input parameter. The name of your procedure is GetEmployeeHistoryDetail so I assume you have at least one output parameter as second parameter (to get the details of the employee history back). If that's the case, you should call the procedure like this (assuming you are inside another procedure and want to use the output of the inner procedure in a scalar or table variable):

declare someOutputVariable bigint;
CALL "PAYROLLDBTEST"."ABS_GetEmployeeHistoryDetail" (:EmpID, :someOutputVariable);

Or in case your out parameter is a table variable. someTableVariable could also be directly an output parameter of your outer procedure:

PROCEDURE "SOMESCHEMA"."yourProcedure" (
      in someInputParameter1 NVARCHAR(255),
      in someInputParameter2 BIGINT,
      out someOutputParameter1 BIGINT,
      out yourSubProcedureOutputParameter "SOMESCHEMA"."some_tabletype") 
   LANGUAGE SQLSCRIPT
   SQL SECURITY INVOKER AS
BEGIN
    // ... (other code logic)
    CALL "PAYROLLDBTEST"."ABS_GetEmployeeHistoryDetail" (:EmpID, :yourSubProcedureOutputParameter);
END;

Comments

[mark]

still gettting error , this time it says sql syntax error: incorrect syntax near "."

[mark]

I tried that but gave me error on same line: SAP DBTech JDBC: [257] (at 2191): sql syntax error: incorrect syntax near ".": line 34 col 27 (at pos 2191) I have added the whole procedure in my question kindly check.

[programmer]

@danierl kullmann I am looking for calling a procedure dynamically. Meaning I have a procedure name in a variable (Ex: dyn_sp := 'MY_STORED_PROC';) how do i call this dynamically from another procedure (CALL dyn_sp(:var1, :var2);

[daniel kullmann]

@programmer You can use EXEC to execute a SQL command in a varchar. But if you want to have result values back from calling the stored procedure, this is probably not going to work.

[programmer]

I need the values back as well. Is there a way? Really appreciate your help!