Hello Tom,

I have a question for you regarding storing SQL in a table to be executed in a client application to either display reports or process business rules that differ between customers using our system. I currently do this in a reporting app (using MS Access), and it seems to work quite well. However, I have been told that this is a bad practice, and would like your opinion.

Basically, we are a “software as a service” provider and have a number of customers using our system (each with their own instance/schema), and although their business processes and practices are similar, they differ in terms of their reporting requirements. So, what I do is store the SQL for a report in a report table (treport) in their schema, and the parameters of the report in a child table (treport_param).

CREATE TABLE TREPORT
(
REPORT_ID NUMBER(22) NOT NULL,
REPORT_GROUP_ID NUMBER(22) NOT NULL,
REPORT_NAME VARCHAR2(50 BYTE) NOT NULL --Report SQL Stored here,
DESCRIPTION VARCHAR2(4000 BYTE),
REPORT_SQL VARCHAR2(4000 BYTE),
ACTIVE_FLAG VARCHAR2(1 BYTE),
Etc.

CREATE TABLE TREPORT_PARAM
(
REPORT_PARAM_ID NUMBER(22) NOT NULL,
REPORT_ID NUMBER(22) NOT NULL,
DISPLAY_NAME VARCHAR2(50 BYTE),
TOOL_TIP_TEXT VARCHAR2(50 BYTE),
CONTROL_TYP VARCHAR2(50 BYTE),
TEXT_TYP VARCHAR2(50 BYTE),
CONTROL_WIDTH NUMBER(22),
CONTROL_INPUT_MASK VARCHAR2(50 BYTE),
DEFAULT_VAL VARCHAR2(50 BYTE),
TEXT_REQUIRED_FLAG VARCHAR2(1 BYTE),
COMBO_SQL VARCHAR2(4000 BYTE),
COMBO_COLUMN_COUNT NUMBER(22),
COMBO_COLUMN_WIDTHS VARCHAR2(50 BYTE),
COMBO_LIST_WIDTH NUMBER(22),
COMBO_COLUMN_HEADS VARCHAR2(1 BYTE),
SORT_ORDER NUMBER(5),
CONTROL_HEIGHT NUMBER(22),
Etc.

When a user selects a report to run, my front-end application builds a parameter input form based on the parameters in treport_Param with drop downs etc., and then takes the SQL for the report stored in treport, does a find and replace on the parameters, creates an MS Access pass-through query, and then dynamically builds the report. Once the report is displayed, users can use the built in MS Access functions for sorting, filtering it etc.

Although I know there are commercial tools that do this (like Cognos), this little reporting app. was very quick to build, and users love the flexibility of what they can do with the output and how quickly reports can be added or changed. Plus I didn’t want a thick client application that had all kinds of logic for presenting data. Basically, I wanted a very thin app that simply presented the results of an SQL statement to the user, which 99% of the time they copy and paste to MS Excel to format/manipulate on their own anyway.

I like this for a couple of reasons. First, I can very quickly change and deploy reports by simply changing the SQL for the report in treport to do things like adding/removing columns, adding calculated fields, sorting differently etc., and the front-end will dynamically display the changes the next time the user runs the report. So, from a business perspective, I can be very responsive to customer requests.

Second, source code control and release management is much less time consuming. Currently, all clients are at the same version of our system, and whenever we make changes to a database object, we try to upgrade all customer schemas as quickly as possible so that we don’t have the nightmare of supporting multiple versions of our system. So, if I were to have the report logic in packages or views, then every time a customer wanted to make a change to a report, I would have to do a release for all customers to ensure they are all on the same version of the database, and then have a bunch of condition statements that basically say “If customer X, run report with these columns, but if customer Y, do something different”, or something like that. Then, someone else has the reporting package already checked out to make changes for another customer, so I have to wait, and on and on it goes. So, since changing a report is effectively configuration, it is very easy to simply make the changes to the SQL in the report table and be done with it.

However, here is what I think is wrong with doing this:
1. I am not using bind variables. Instead, I build an MS Access pass-thru query using inline SQL in VBA. This obviously is not as efficient as using bind variables, but the majority of reports run in seconds anyway as they do not return reams of data.
2. I am not using a source code control tool for managing the report SQL. Instead, the SQL is stored in a table, and one would have to turn on auditing or something to track changes to reports over time (if it was important to do so). Or, we could extract the table as insert statements and store that in a source code control tool before any changes were made.

As you can probably tell, I am from the business side of our organization and not the development side, and know just enough to be dangerous. However, fast deployment = happy customers, and happy customers = $$ and growth. Furthermore, less time doing system administration (version control/release management) and more time actually building new functionality into the system to attract even more customers seems to be a better use of resources.

Anyway, since I don't want to be a complete "coding cowboy" as I have been described in the past, my question: Is it a good or bad practice to store SQL in a table as I am doing in this little app?

Thank you in advance for your time.