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).
The BNF definition for dynamic SQL is as followed:
Example: A generic paging stored procedure
Let's look at the code for above example:
CREATE PROCEDURE GetRows
(IN tablename VARCHAR(100), IN sortfields VARCHAR(500),
IN fromrow int, IN numrows int)
READS SQL DATA
DECLARE query VARCHAR(1000);
SET query = 'select TOP '+CAST(numrows AS VARCHAR(10))+','+
CAST(fromrow AS VARCHAR(10))+' * from '+tablename;
IF sortfields<>'' THEN
SET query = query + ' order by '+sortfields;
EXECUTE IMMEDIATE query;
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.