Skip to Content
0
Oct 29, 2019 at 04:54 PM

Sap hana sql script how to declare dynamic cursor

713 Views

Hi All,

We have trying to take an input table to the procedure and then trying to loop through the table and trying to replace a value in each row of the table.

So far, we have been able to successfully execute the below piece of code (replaces the BEDNR value in each row with a value start in 500)

CREATEProcedure PROC_TEST_RANDOM_1

LANGUAGE SQLSCRIPT

ASBEGIN

DECLARE RAND_CUR INT;

DECLARE RAND_CUR_NO INT;

DECLARECURSOR RANDOM_CURSOR FORSELECT * FROM"schemaname"."ABAPTABLES_EKPO";

RAND_CUR := 500;

FOR CUR_ROW AS RANDOM_CURSOR DO

UPDATE"schemaname"."ABAPTABLES_EKPO"

SET BEDNR = RAND_CUR

WHERE EBELN = CUR_ROW.EBELN

AND EBELP = CUR_ROW.EBELP;

RAND_CUR := RAND_CUR + 1;

ENDFOR;

END;

Now what we’re trying to do is replace the table name with an input table name (highlighted in yellow below). Basically in the DECLARECURSOR statement, we’re getting a syntax error, saying that a Cursor cannot be declared as a Scalar. We are looking for ways to declare the cursor from an input table variable (IN_TBLNAME)

CREATEProcedure PROC_TEST_RANDOM_1 ( ININ_TBLNAMENVARCHAR(40) )

LANGUAGE SQLSCRIPT

ASBEGIN

DECLARE RAND_CUR INT;

DECLARE RAND_CUR_NO INT;

DECLARECURSOR RANDOM_CURSOR FORSELECT * FROM :IN_TBLNAME;

RAND_CUR := 500;

FOR CUR_ROW AS RANDOM_CURSOR DO

UPDATE :IN_TBLNAME

SET BEDNR = RAND_CUR

WHERE EBELN = CUR_ROW.EBELN

AND EBELP = CUR_ROW.EBELP;

RAND_CUR := RAND_CUR + 1;

ENDFOR;

END;

We tried Dynamic Sqlscript to declare cursor dynamically

CREATEProcedure PROC_TEST_RANDOM_POC ( IN IN_TBLNAME NVARCHAR(40) )

LANGUAGE SQLSCRIPT

DEFAULTSCHEMA"ZSDI"

ASBEGIN

Declare RAND_CUR_Value INTEGER := 500;

DECLARE RAND_CUR_NO INTEGER;

Declare Query Nvarchar(256) := '

DECLARE RAND_CUR INTEGER;

DECLARE CURSOR RANDOM_CURSOR FOR SELECT * FROM' ||:IN_TBLNAME;

EXECUTEIMMEDIATE(:Query);

--RAND_CUR := :RAND_CUR_Value + 1;

FOR CUR_ROW AS RANDOM_CURSOR DO

UPDATE ABAPTABLES_EKPO

SET BEDNR = RAND_CUR_value

WHERE EBELN = CUR_ROW.EBELN

AND EBELP = CUR_ROW.EBELP;

RAND_CUR_value := RAND_CUR_value + 1;

ENDFOR;

END;

Following error we are getting while activating

Could not execute 'CREATE Procedure PROC_TEST_RANDOM_POC ( IN IN_TBLNAME NVARCHAR(40) ) LANGUAGE SQLSCRIPT DEFAULT ...'

SAP DBTech JDBC: [1287]: identifier must be declared: line 12 col 16 (at pos 399)