NexusDB AWE DLL (40 installations; binary) added to your shopping cart.

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:

<ExecuteStatement> :== "EXECUTE" "IMMEDIATE" <SimpleExpression>

Example: A generic paging stored procedure

Let's look at the code for above example:

DROP PROCEDURE IF EXISTS GetRows;
CREATE PROCEDURE GetRows
  (IN tablename VARCHAR(100), IN sortfields VARCHAR(500),
   IN fromrow int, IN numrows int)
READS SQL DATA
BEGIN
  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;
  END IF;
  EXECUTE IMMEDIATE query;
END;

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

  call GetRows('table1', 'field1 desc', 25, 5);

 

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.

Home | Site Contents | Documentation | NexusDB Manual V4 | Overview of new Features in NexusDB V3