How to Code SQLScript in SQL Console without Function or Procedure

SAP HANA SQLScript developers can execute SQL commands on SQL Console other than SELECT without creating stored procedure by using Anonymous Block which enables SQLScript programmer to run any code which can be used in procedures. If you are a SQLScript developer working on SAP HANA Web-based Development Workbench IDE or using SAP HANA Studio, you can execute DML statements without to code in a stored procedure or function, etc. using the Do Begin End syntax of anonymous blocks.

For database developers who have experienced other data platform tools like SQL Server Management Studio, it is strange to create procedures in order to execute simple SQL scripts like creating variables in SQL code.
Although SQL developer can run SELECT statements or CREATE commands on SQL Console, building small code blocks which do not belong to a database object is not possible without using procedures, etc until anonymous blocks are introduced.

SAP HANA SQLScript enables SQL developers to execute independent sql code fragments which are not in a stored procedure or user function by defining anonymous blocks.

An anonymous block in SQLScript is defined by DO command immediately followed by BEGIN/END where the SQL script body takes place between two.

In order to visualize the use of DO BEGIN END SQLScript code block:


DO BEGIN

 DECLARE lv_url VARCHAR(250) := 'kodyaz.com';

END;

Code

SQLScript Anonymous Block in SQL Console for SAP HANA developer

It is confusing when the developer get the error message, if he/she ommits the anonymous block declaration and only executes the data variable declaration on SQL Console as follows


declare lv_url varchar(250) := 'kodyaz.com';

Code

declare variable error in SQL Console without Do Begin End block

4:04:40 PM (SQL Editor) Could not execute 'DECLARE lv_url VARCHAR(250) := 'kodyaz.com''
Error: (dberror) 257 - sql syntax error: incorrect syntax near "DECLARE": line 22 col 0 (at pos 1)

The solution for developing SQL codes in SQLScript on SQL Console without procedures is anonymous blocks. It enables testing the SQL script for the developer before deploying it into a solution.


DO BEGIN

 DECLARE v_url NVARCHAR(250) := 'www.kodyaz.com';

 SELECT LENGTH(v_url) FROM dummy;

END;

Code

sample SQLScript code with anonymous block on SAP HANA database

SQLScript programmers can check Length SQL string function for details at SAP Help Portal