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:
DECLARE lv_url VARCHAR(250) := 'kodyaz.com';
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';
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.
DECLARE v_url NVARCHAR(250) := 'www.kodyaz.com';
SELECT LENGTH(v_url) FROM dummy;
SQLScript programmers can check Length SQL string function for details at SAP Help Portal