I must confess I never heard of Dynamic SQL. But suddenly around me everyone started talking on Dynamic SQL. What is dynamic SQL?
Dynamic SQL is a SQL statement that is created at runtime. Such is the definition.
I realised that I had already created quite some dynamic SQL without realising that such construction is Dynamic SQL. As an example, I have created a data warehouse that is fed via SQL statements. A number of these statements are created at runtime only. As an example, one may think of a construction like:
set $targettable = XX insert into $targettable select * from source
In the example above, the SQL that is fired will be „insert into XX select * from source“. However if $targettable is set at YY, the generated SQL will be „insert into YY select * from source“.
Hence dynamic SQL allows you to write one skeleton SQL statement that can be used in different circumstances. This allows you to write smaller scripts that are easier to maintain.
Another situation where dynamic SQL can be used, is the handling of user input.
Suppose one has input fields where a user may insert some data. Such data are then stored in a variable that is subsequently inserted into the database. To accomplish this, the user input is captured in a variable that can be subsequently inserted into the database. Something like:
set $input = "data from input form" insert into targettable inputfield ($input)
Around me, some pushback on dynamic SQL, could be heard. The main problem is that dynamic SQL might be difficult to maintain as one may not know what SQL is exactly generated. I am inclined to see that as sloppy programming. A good programmer can always capture the generated SQL and show this to the user. This could be handy when the script must be debugged.
Another issue was that the statements that generate the SQL, can be quite difficult. This can certainly the case. I have seen scripts that were needless complicated to generate a SQL statement. However, good programming techniques should prevent this.