Skip to Content

Sap hana sql script how to declare dynamic cursor

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)

Add a comment
10|10000 characters needed characters exceeded

Related questions

1 Answer

  • Posted on Oct 29, 2019 at 06:25 PM

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

    Add a comment
    10|10000 characters needed characters exceeded

    • 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

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.