Concepts - Dynamic SQL

Preface

LLBLGen used stored procedures and for the successor, LLBLGen Pro, stored procedures were considered as well. However, because one of the design goals was to be database independent and we wanted to make it easy for the users of the generated code to produce queries on the fly and query for entity objects in a flexible manner, stored procedures produced a problem: their interface is set in stone and when you want to do something that is not programmed in the available set of procedures, you have to add another procedure.

Dynamic SQL and Dynamic Query Engines

The solution is to use dynamic SQL, which means all the queries are generated in real time. These SQL queries are fully parameterized and are created in a Dynamic Query Engine (DQE). Each database driver comes with its own DQE, which is tailored to create queries especially for that particular database. Because today's RDBMS's are fully equipped with an optimizer that will keep execution plans of queries executed, including queries not formulated in a procedure, you don't loose any speed when it comes to execution and query text compilation. Because dynamic SQL contains only those statements which are relevant to the action to perform, in a lot of cases the dynamically created query is faster than a stored procedure: an update with just one SET statement is faster than an update setting all fields to new values, because then you only have to write one stored procedure.

Flexibility

When a table has 2 or more foreign key constraints, and thus the related entity has 2 or more m:1 relations and 1 or more m:n relations, creating stored procedures for all possible filters on that table for entity retrieval is a tedious task, because it can become quite a lot of procedures: for every combination of foreign key fields you have to write a different select statement. As a work around, you can use parameters which can be NULL in a stored procedure. However you then have to place every parameter in the WHERE clause, even if just one parameter is not NULL, and use COALESCE to test for NULL. These kinds of queries are slower than their dynamic counterparts. See for analysis, test code and comments the following links:
Stored procedures vs dynamic queries
Benchmark code

The dynamic nature of the DQE in LLBLGen Pro also has the advantage of being able to construct SQL using normal program constructions, like classes and enumerations. Moreover, these queries are then database independent, because they use constructions formulated in the LLBLGen Pro runtime library, like predicate expressions. See the section about Using the generated code for details about how to write dynamic queries in your own code, or look into the code that is produced by the Typed List templates.