Dynamic SQL

Dynamic SQL is an application that uses dynamic code to increase flexibility in querying a database. Dynamic code is generated at execution of a SQL application by inserting statements into variables.

Dynamic code is one of two methods for coding applications for SQL. The other is called stored procedures. The difference between the two is how the application calls information from the database using certain types of statements and processes.

As stated, dynamic SQL statements allow the application to create code before it is executed. In contrast, stored procedures is code that exists unaltered at execution. Dynamic coding is when statements are entered into variables that allow for the application to create calls that are unique to query table data. These calls may be different every time they are executed, since they are created as needed.

Stored procedures are written to predict every possible variable in querying the database. In small databases this isn’t necessarily difficult, but as databases become bigger and more complex, it becomes almost impossible to predict what range of requests will be made.

By placing statements within variables, a programmer allows the user to customize their data requests. The most common statement is “WHERE.” The WHERE statement tells the application that if certain tables are requested, and they meet certain criteria or relationships with other tables, then return the results in a specific way. This takes the prediction out of writing stored procedures and creates flexibility for a programmer and user.

The advantage of using dynamic SQL statements is more flexibility in coding an application, flexibility for a user to find the information they want formatted the way they need, and expansion without adding more stored procedures.

There are draw backs to using dynamic SQL, however. Stored procedures execute a lot faster than dynamic SQL statements, since the application is only looking for commands. Using dynamic SQL statements requires the database application to generate the code then execute it as requested, which means it is processing more before it actually starts looking for results. It is then formatting the results on the fly as well, which further slows the application.

Other drawbacks are security issues and potential errors. Increased flexibility means holes in the code are easier to find and exploit. Also, dynamic SQL statements cannot be tested for every instance, unlike stored procedures. Since there are no parameters, there is an increase in flexibility for large databases, and the code is not created until execution, testing for errors and conflicts is more difficult, if not impossible.

There is an on-going debate within SQL communities as to whether dynamic code is good or not. Both types have advantages and disadvantages, with both having obvious necessity in certain situations. The general consensus seems to be: it depends. The more complex a database becomes, the more the need for flexibility in searches and reporting. Dynamic SQL appears to have value, but should be used with constraint to minimize the disadvantages.