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

Is this question related to SQL Server in any way? You've added the `sql-server` tag but I cannot see the connection. — Andriy M, Jun 11 '14 at 07:46
Original procedure was in SQL so thats why tagged and also SQL syntaxes are used in it — mark, Jun 11 '14 at 07:53
If it was in SQL, you should probably tag the question with `sql` instead. SQL is a language, SQL Server is a product that happens to use (a variation of) that language. Many other products use SQL too, so tagging your question as related specifically to SQL Server seems confusing. — Andriy M, Jun 11 '14 at 07:56
@mark, did you get this working? 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); — programmer, Mar 16 '16 at 15:09

hoffman · Accepted Answer · 2014-06-09T12:50:23.720

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;

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. — mark, Jun 11 '14 at 05:31

score 0 · Answer 2 · answered Jun 09 '14 at 08:40

0

Procedures should be called from the _SYS_BIC schema, please try the following call:

call "_SYS_BIC"."PAYROLLDBTEST/ABS_GetEmployeeHistoryDetail"(:EmpID)

answered Jun 09 '14 at 08:40

Kirill K.

31
5

still gettting error , this time it says sql syntax error: incorrect syntax near "." – mark Jun 09 '14 at 10:10

score 0 · Answer 3 · answered Mar 17 '15 at 09:50

0

You can't do a CALL inside the FROM part of a SELECT statement.

Your procedure should work like this:

CALL "PAYROLLDBTEST"."ABS_GetEmployeeHistoryDetails"(:EmpID,:TEMP);

SELECT fields
FROM (
  SELECT fields, case1, case2
  FROM ( SELECT * FROM :TEMP ) InnerQuery
) OuterQuery
WHERE (conditions);

answered Mar 17 '15 at 09:50

daniel kullmann

13,653
8
51
67

@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); – programmer Mar 16 '16 at 15:09
@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. – daniel kullmann Mar 17 '16 at 06:29
I need the values back as well. Is there a way? Really appreciate your help! – programmer Mar 17 '16 at 12:14

not able to call procedure within procedure in HANA Studio

3 Answers3