NexusDB

What is Dynamic SQL

Dynamic SQL statements are SQL statements which are constructed at runtime and passed to the SQL engine for execution. A simple example would be a stored procedure that returns a number of records for paged access to data. While you could of course handle that by creating dynamic SQL creation on the client side, it is much more elegant to create one single stored procedure like GetRows(TableName, SortFields, From, Number).

BNF

The BNF definition for dynamic SQL is as followed:

Example: A generic paging stored procedure

Let's look at the code for above example:

and we can now simple page any table ordered by any field by simply calling something like

That's it. Very easy and at the same time very powerful. Of course the usage is not limited to the use of SELECT statements only but you can construct and execute ANY valid NexusDB V3 SQL statement in this way. It can be used in any scope being that a simple script, a stored procedure or function or even triggers.

Dynamic SQL in combination with the meta tables and the CURSOR support (which I will show you in one of the next posts of this series) will enable you to write much more powerful SQL functions, procedures and triggers. It will make NexusDB V3 SQL a programming environment with which you will be able to solve even the most complex problems.