SAP HANA Stored procedure without any input parameter

[former_member588310]

I need to create a stored procedure that updates the root account id for a account in a table. Mainly I can use ancestor hierarchy function to find the root node for each account , but how can I find the root account id and update that for each account in my table if that needs to be done for each and every record of a table, and without passing any input parameter in the procedure. Can I use for loop on the table and fetch each record and then find its root account and update ?

[pfefferf]

I think there are more solutions for your requirement. But without more details it is hard to give the right direction. What of course works is to use a SQLScript cursor which allows you to process every record you need to analyze. As you said a for loop in combination with an index-based cell access would work too.

But keep in mind that both options are not the best regarding performance.

[former_member588310]

I want to do it in one shot for all records without using cursor or for loop as it is throwing error when number of records are many.

This is my code :

PROCEDURE "SCHEMA"."PACKAGE::ROOT_ACCOUNT_ID" ( )

LANGUAGE SQLSCRIPT

SQL SECURITY INVOKER

--DEFAULT SCHEMA <default_schema_name>

----READS SQL DATA

AS

BEGIN

DECLARE CURSOR c_cursor1 FOR

SELECT ACCOUNT_ID, PARENT_ACCOUNT_ID FROM "TABLE";

FOR r1 AS c_cursor1 DO

DECLARE TOP_NODE NVARCHAR(100);

DECLARE COUNT_PINT;

DECLARE SQL_STRN VARCHAR(3000);

/**** Retrieving top parent node of the Hierarchy ******/

TOP_NODE1 = SELECTTOP 1 DISTINCT NODE_ID AS"PARENT_ACCOUNT_ID",HIERARCHY_DISTANCE

FROM HIERARCHY_ANCESTORS (

SOURCE HIERARCHY (

SOURCE (

SELECT

ACCOUNT_ID AS NODE_ID,

PARENT_ACCOUNT_ID AS PARENT_ID

FROM "BASE_CV_ON_TOP_OF_TABLE"

ORDER BY NODE_ID

))START WHERE LTRIM(NODE_ID,0) = LTRIM(r1.ACCOUNT_ID,0)

)

ORDER BY HIERARCHY_DISTANCE ASC;

SELECT COUNT(*) INTO COUNT_P FROM :TOP_NODE1;

IF COUNT_P = 0 THEN

TOP_NODE:='';

ELSE

SELECT PARENT_ACCOUNT_ID INTO TOP_NODE FROM :TOP_NODE1;

ENDIF;

SQL_STR := 'UPDATE "TABLE" SET "ROOT_ACCOUNT_ID" = '''||TOP_NODE||''' WHERE ACCOUNT_ID = '''||r1.ACCOUNT_ID||''' ';

EXECUTE IMMEDIATE (:SQL_STR);

ENDFOR;

END;

[pfefferf]

Maybe you can post the error which is thrown (just saying there is an error is not really helpful).

[shanthi_bhaskar]

why can't use Table functions, Stored procedure best used with input parameter.