Skip to Content
Technical Articles
Author's profile photo Dorothy Eiserman

Running a HANA Table Function as an anonymous block in SQL Console

We often use anonymous blocks to view all the logic pieces of a table function from within the SQL Console, especially the output of table variables.

They are useful when testing code in the HANA SQL console as it allows imperative (for e.g. loops and conditionals) or declarative statements (variables and table variables) to be used. They also have no corresponding object that will be created in the catalog so there is very little overhead.

Here are the steps I follow:

Step 1: Go to repository and fetch the latest code of the table function

Step 2: Paste the code into the SQL console of the editor in the Web IDE:

https://<server name>:<port>/sap/hana/ide/catalog/

 

Step 3: Change BEGIN to DO BEGIN – this opens the anonymous block

Before

After

 

Step 4: Parameter handling: If your table function has parameters then they will need to be copied down till after the BEGIN statement, then declared and instantiated.

Copy the parameters from function header

Option 1: Paste them after begin and declare as normal variables

Paste them after BEGIN

Adjust coding to be a declaration and instantiation

Option 2: Call anonymous block with parameter clause:

 

Step 5: Clean up the VAR_OUT section

Option 1 – remove the VAR_OUT completely

Remove

Remove

Place a semi-colon after last statement.

Before:

After:

Option 2 – replace RETURN :var_out; with SELECT * FROM :var_out;

Remove

Add

 

Step 6: Execute: Highlight code from DO BEGIN to END; and then press  

 

 

Step 7: If you want to see the output of each table variable, you simply put a SELECT * FROM :<table variable name>; after each one.

A tab will open with the results of each table variable you selected from:

Happy coding! Look forward to hearing any comments/suggestions.

Assigned Tags

      4 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Shanthi Bhaskar
      Shanthi Bhaskar

      Thanks, Good blog. Appreciate your time.

      Author's profile photo Dorothy Eiserman
      Dorothy Eiserman
      Blog Post Author

      Thanks Shanthi, I know that sometimes the things we think are obvious are useful for others. I'm still learning new things every day!

      Author's profile photo abilash n
      abilash n

      Thanks for sharing this solution which helps in testing the code easier...

      Author's profile photo Dot Eiserman
      Dot Eiserman

      I’ve consolidated all my profiles into central profile, if you have a comment/question, please don’t comment on this old blog, please go to the following repost or send me a message:

      https://blogs.sap.com/2019/09/24/running-a-hana-table-function-as-an-anonymous-block-in-sql-console-2/