Search code examples
dynamic-sqlhana

Dynamic SQL in SAP HANA Stored Procedures

I want to create a stored procedure in HANA, which does the following :

  • Accepts IN parameter of Table type.
  • Accepts another IN parameter of Varchar type.
  • Filters some rows from a existing table based on column values of first input parameter.
  • Tries to sort (ORDER BY) rows based on criteria given by second input parameter.

Here is the stored procedure that I want to create

CREATE PROCEDURE DEMO_PROD_EXAMPLE_DYNAMIC(IN TEMPLIST PRODLISTTYPE,IN ORDERSTRING VARCHAR(200))
AS
BEGIN
OUTVAR = SELECT * FROM DEMO_PRODS WHERE NAME IN (SELECT NAME FROM :TEMPLIST);

SELECT * FROM :OUTVAR ORDER BY :ORDERSTRING DESC;

END;

I am facing the following hurdle :

  • In the above procedure, ordering does not happen at all! If I hardcode the column name, like SELECT * FROM :OUTVAR ORDER BY ID DESC; it works.
  • If I try to create a dynamic SQL query like SELECT * FROM'|| :OUTVAR||' ORDER BY '||:ORDERSTRING||'DESC'; I get an exception

    not allowed to use varchar and table type with concatenation operator.

How do I sort the resultset based on a dynamic condition (column name/s passed to procedure) in HANA.

TIA

Solution

  • You have to use EXECUTE IMMEDIATE to execute a dynamic SQL statement:

    EXECUTE IMMEDIATE 'SELECT NAME FROM DEMO_PRODS ' || :ORDERSTRING || ' DESC';

    However, with dynamic SQL you cannot use table typed variables to select from. So you need to find another solution for SELECT * FROM DEMO_PRODS WHERE NAME IN (SELECT NAME FROM :TEMPLIST);. Using a global temporary table is one:

    INSERT INTO TEMPORARY_TABLE (SELECT * FROM DEMO_PRODS WHERE NAME IN (SELECT NAME FROM :TEMPLIST);
EXECUTE IMMEDIATE 'SELECT NAME FROM TEMPORARY_TABLE ' || :ORDERSTRING || ' DESC';