title: Dynamic SQL in SAP HANA Stored Procedures
body: <p>I want to create a  stored procedure in HANA , which does the following :</p>
  
  <ul>
  <li>Accepts IN parameter of Table Type.</li>
  <li>Accepts another IN parameter of varchar type.</li>
  <li>Filters some rows from a existing table based on column values of first input parameter.</li>
  <li>Tries to sort (ORDER BY) rows based on criteria given by second input parameter.</li>
  </ul>
  
  <p>Here is the stored procedure that I want to create </p>
  
  <pre><code>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;
  </code></pre>
  
  <p>I am facing the following hurdle :</p>
  
  <ul>
  <li>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 .</li>
  <li>If I try to create a dynamic SQL query like SELECT * FROM'|| :OUTVAR||' ORDER BY '||:ORDERSTRING||'DESC'; I get an exception that it is not allowed to use varchar and table type with concatenation operator.</li>
  </ul>
  
  <p>How do I sort the resultset based on a dynamic condition (column name/s passed to procedure) in HANA.</p>
  
  <p>TIA</p>