Create Procedure fails

Hi All,

I am working on a product which integrates with SAP B1 and I am trying introduce support for SAP HANA. As part of our product configuration I need to insert some code into the B1 SBO_SP_TransactionNotification stored procedure. I have converted our current MS SQL code into a HANA format but I have been experiencing issues when trying to run the create procedure statement.

I thought it might be failing due to it being a B1 stored procedure so I have tried creating a stored procedure within the B1 database which replicates the basic idea of what I am trying to do. I am executing this statement from HANA Studio.

Create Procedure TestVariables(in SONum int) LANGUAGE SQLSCRIPT AS docTotal decimal;BEGIN   BEGIN     DECLARE a INT :=12;          select "LogInstanc" into a from "ORDR" where "DocEntry" = :SONum;          if a > 0 then      select "DocTotal" into docTotal from "ADOC" where "DocEntry" = :SONum and "LogInstanc" = :a and "DocType"=17;    end if;  END;  Select :docTotal from DUMMY;END;

This fails with following error:-

Could not execute 'Create Procedure TestVariables(in SONum int) LANGUAGE SQLSCRIPT AS docTotal decimal; BEGIN BEGIN ...' in 25 ms 250 µs .
SAP DBTech JDBC: [257] (at 334): sql syntax error: line 12 col 1 (at pos 334)


Could not execute 'Select :docTotal from DUMMY' in 25 ms 98 µs .
SAP DBTech JDBC: [7] (at 7): feature not supported: line 1 col 8 (at pos 7)


Could not execute 'END' in 24 ms 547 µs .
SAP DBTech JDBC: [257]: sql syntax error: incorrect syntax near "END": line 1 col 1 (at pos 1)

If I take out the "if" statement and run it the procedure is created. I have looked at some of the B1 stored procedures and they appear to be a lot more complex than this with multiple levels of nesting and conditional statements.

I am very new to HANA and I have read the documentation on the sqlscript but cannot see anything that would suggest why this wouldn't work.

Is anybody able to help on this issue?

Thanks,

Darren