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