Skip to Content
-1

How to call output of store procedure in HANA scripted Calculation View?

Hi Gurus,

I'm newbie to HANA.

For Example i have a table.

Table Name : EMP

tablee.png

Now I'mm writing Store procedure to get data.

CREATE PROCEDURE PROCEDURE_EMP ( IN EMP VARCHAR(1), OUT RESULT TABLE ( EMP VARCHAR(1), EMPNA VARCHAR(15), EMPGE VARCHAR(10)))

LANGUAGE SQLSCRIPT

SQL SECURITY INVOKER

DEFAULT SCHEMA "BW"."SCHEMA"

BEGIN

RESULT = SELECT EMPNO, EMPNAME, EMPGENDER FROM (SOMETABLE) WHERE EMPNO = EMP

END

CALL PROCEDURE PROCEDURE_EMP ( 1, ?)

IM GETTING OUT PUT.

DO I NEED TO GIVE ONLY '?' CAN I USE RESULT INPLACE OF '?' LIKE

CALL PROCEDURE PROCEDURE_EMP ( 1, RESULT)

CALCULATION VIEW

BEGIN

how i need to call procedure??

CALL PROCEDURE PROCEDURE_EMP ( 1, ?) [[[like this]]]

VAR_OUT = SELECT * FROM ( WHAT I NEED TO WRITE??) DO I NEEED TO USE '?'

END

Thanks in advance.

Kind Regards,

Venkatesh

tablee.png (3.9 kB)
Add comment
10|10000 characters needed characters exceeded

  • hi Venkatesh,

    it seems like you have all the pieces in place. calling the stored procedure as: call <procedure_name> (1, :VAR_OUTPUT);

    your :var_output will contain the output of the stored procedure.

    you can also use a graphical calculation view and consume a table function (instead of your stored proc)

  • Sergio,

    firstly thanks for the reply :)

    //it seems like you have all the pieces in place. calling the stored procedure as: call <procedure_name> (1, :VAR_OUTPUT);//

    but i haven't wrote anything related to VAR_OUTPUT in the code right?? how can i call the store procedure using VAR_OUTPUT?? is there something i missing here??

    so if call the procedure as you said var_output will contain the output of the stored procedure.

    and the i can write a select statement in CV

    VAR_OUT = select * from VAR_OUTPUT.

    am i right??


    venkatesh

  • Get RSS Feed

1 Answer

  • Sep 04, 2018 at 03:52 PM

    Scripted calculation views are obsolete and shouldn't be used any longer. We would strongly suggest you instead use a Graphical Calculation View and turn your stored procedure into a Table Function. You will save yourself the trouble of having to convert to that anyway in the future.

    Add comment
    10|10000 characters needed characters exceeded