The benefits of using a relational database management system over other types of data storage such as flat files, spreadsheets, hierarchical databases, etc., is the ability to look at the information it contains in various ways and to give us a better way of managing that content. What we learn from processing and analyzing the data leads to better insight and questions about the nature of things. For example, if we get information about annual sales volumes, we might ask which product or month is the most (or least) profitable? We might want to change the columns on the report, or sort it in a different way depending on what department we work in.
Developing applications that provide flexible paths to retrieve and manage information in large databases is one of the big challenges we face as builders of systems. Our ability to anticipate future requests and build that flexibility into the system at design time requires experience, insight, and judgment. Translating such a design into a deliverable project depends on the amount of time, the resources available, and the understanding of the technologies we are working with.
To meet this objective, most database systems provide the facilities for running SQL code directly against the database engine. ODBC has the call SQLExecDirect, ADO uses the Command object, and most others have similar calls. The purpose is to allow the developer the flexibility of creating an SQL statement within the application based on user input to determine what information to return.
In this post, we will address the issues surrounding Dynamic SQL and its various uses. The main points of this chapter include:
For more information take a look at this article I wrote...