Sap hana sql script how to declare dynamic cursor

[#1]

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)

[pfefferf]

Why do you need a cursor for that? A simple Update (which can be executed in a dynamic way) can do the same.

[Hemanth2406]

we need to update each row of a column in a table with some values based on some condition. So we are using cursors to get each row.

If we use Update it will update only value for that total column.

My requirement is

col1 col2 col3

1 XYZ NULL

2 ABC 123

I need to change col3 value to some series of number like

col1 col2 col3

1 XYZ 987

2 ABC 876

To change value of col3 of each row we are using cursor.Here we are passing table name as a input parameter.

Thanks in advance