Skip to Content
0

How to call function from the procedure in SAP HANA?

Nov 13, 2017 at 10:00 AM

247

avatar image

Hi,

Here i create a funtion to calculate sales amount based on quantity and Unit selling price.

 create function "KABIL_PRACTICE"."SALES_AMOUNT" 
 ( Q integer, USP integer)
 returns result decimal (34,2)
 language SQLSCRIPT
 SQL SECURITY INVOKER as
 Begin 
 result := :Q * :USP;
 END;

i tried to use that the above function inside the procedure but it throws an error..

This is my procedure code:

create procedure "KABIL_PRACTICE"."PROC_SALES_INVENTORY"
(
IN "P_ID" integer,
IN "P_NAME" nvarchar(35),
IN "QTY" integer, 
OUT Result "KABIL_PRACTICE"."TT_SALES_PRODUCT_INVENTORY")
language SQLSCRIPT
as begin
declare PROD_ID integer;
declare INV integer;
declare EP decimal (34,2);
declare SALES_AMOUNT Decimal(34,2);
select count(*) into PROD_ID from "KABIL_PRACTICE"."PRODUCT_ITEM_PROC" where "P_ID" = :P_ID;
select "Stock" into INV from "KABIL_PRACTICE"."PRODUCT_MASTER_PROC"  where "P_ID" = :P_ID;
select "USP" into EP from "KABIL_PRACTICE"."PRODUCT_ITEM_PROC"  where "P_ID" = :P_ID;
IF INV >= :QTY THEN
call "KABIL_PRACTICE"."SALES_AMOUNT" (:QTY,:EP);
/*--SALES_AMOUNT := (:QTY * :EP);--*/
insert into "KABIL_PRACTICE"."SALES_PROC" ("P_ID","P_NAME","QTY","Sales_AMount") values (:P_ID,:P_NAME,:QTY,:SALES_AMOUNT);
update "KABIL_PRACTICE"."PRODUCT_MASTER_PROC" set "Stock" = "Stock" - :QTY where  "P_ID" = :P_ID;
END IF;
Result = select "S_ID","P_ID","P_NAME","QTY","Sales_AMount" from "KABIL_PRACTICE"."SALES_PROC";
end;


Error:

Could not execute 'create procedure "KABIL_PRACTICE"."PROC_SALES_INVENTORY" ( IN "P_ID" integer, IN "P_NAME" ...' SAP DBTech JDBC: [328]: invalid name of function or procedure: SALES_AMOUNT: line 17 col 23 (at pos 646)

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

1 Answer

Lars Breddemann
Nov 21, 2017 at 03:51 AM
0

You simply use your UDF as if it was a builtin function.

do begin
declare myvariable nvarchar(30);

    myvariable := "COOL_SCHEMA"."COOLER_FUNCTION_NAME" ('<awesome input parameter>');
    select :myvariable from dummy;
end;
Share
10 |10000 characters needed characters left characters exceeded