Error in procedure

[former_member226419]

Hi,

I wrote this procedure but giving me below error.

I have couple of more questions regarding procedure:

a.) I can find lots of difficulties regarding syntax of Procedure.

     Can someone assist me with good document on procedure syntax?

B.) We dont have any HELP as we can have in ABAP F1 if we want to refer any syntax for procedure?

c.) What is the diff b/w creating procedures via SQL console and package procedures and which is recommended and why?

drop procedure  insert_bom_2;

CREATE PROCEDURE insert_bom_2

LANGUAGE SQLSCRIPT AS 

V_AVBL_FOR_ISSU_QTY  INTEGER := 0; 

BEGIN 

  DECLARE CURSOR C_INSERT_BOM_2 FOR 

    SELECT * FROM "Sumeet"."BOM"

    ORDER BY MTRL_ID; 

  FOR CUR_ROW AS C_INSERT_BOM_2 DO 

    V_AVBL_FOR_ISSU_QTY := 35; /* hard coded value to 35 */ 

    INSERT INTO "Sumeet"."BOM_2" VALUES (CUR_ROW.MTRL_ID,CUR_ROW.MTRL_NAME, 

        CUR_ROW.WHSE,CUR_ROW.AVBL_QTY,CUR_ROW.ISSU_QTY,V_AVBL_FOR_ISSU_QTY); 

  END FOR;   

END; 

call  insert_bom_2();

Error:

Could not execute 'drop procedure insert_bom_2' in 2 ms 88 µs .

SAP DBTech JDBC: [328] (at 15): invalid name of function or procedure: INSERT_BOM_2: line 1 col 16 (at pos 15)

Could not execute 'CREATE PROCEDURE insert_bom_2 LANGUAGE SQLSCRIPT AS V_AVBL_FOR_ISSU_QTY INTEGER := 0; BEGIN DECLARE ...' in 1 ms 863 µs .

SAP DBTech JDBC: [257] (at 481): sql syntax error: incorrect syntax near "call": line 15 col 1 (at pos 481)

BR

Sumeet

[Former Member]

Hi Sumeet-

All of the syntax which you gave are correct. Except calling the procedure.

To Drop a procedure :

DROP PROCEDURE insert_bom_2;

Creation of Procedure :

CREATE PROCEDURE insert_bom_2
  LANGUAGE SQLSCRIPT AS 
V_AVBL_FOR_ISSU_QTY  INTEGER := 0; 
BEGIN 
  DECLARE CURSOR C_INSERT_BOM_2 FOR 
     SELECT * FROM "Sumeet"."BOM"
     ORDER BY MTRL_ID;      
  FOR CUR_ROW AS C_INSERT_BOM_2
   DO 
    V_AVBL_FOR_ISSU_QTY := 35; /* hard coded value to 35 */ 
    INSERT INTO "Sumeet"."BOM_2" VALUES (CUR_ROW.MTRL_ID,CUR_ROW.MTRL_NAME, 
                                         CUR_ROW.WHSE,CUR_ROW.AVBL_QTY,
                                         CUR_ROW.ISSU_QTY,V_AVBL_FOR_ISSU_QTY); 
  END FOR;   
END; 

Call Procedure :

call insert_bom_2;    

Regarding the materials I am also learning with the SAP materials.

You can also do in HANA Studio HELP->SEARCH

You can also see the below links

http://scn.sap.com/community/developer-center/hana/blog/2012/11/30/the-ultimate-set-of-hana-related-...

http://scn.sap.com/community/developer-center/hana/blog/2013/06/25/want-to-learn-sap-hanawhere-to-st...

Thanks