Technology Blogs by Members
Explore a vibrant mix of technical expertise, industry insights, and tech buzz in member blogs covering SAP products, technology, and events. Get in the mix!
cancel
Showing results for 
Search instead for 
Did you mean: 
DotEiserman
Advisor
Advisor
Note: This blog is the repost of an old blog on an old profile as part of the process of pulling all my content together on a consolidated profile.

Version: HANA 2.0 SP02

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 Based Development Workbench:

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.
Labels in this area