Ryan Thompson works on a project where all database queries had to go through "stored procedures". Now before you call me out on extraneous quotes or wonder, so what's wrong with stored procedures?, I'm not talking about those kind of stored procedures. I'm talking about "stored procedures" — i.e., the technique developed by Ryan's predecessors for the ease of maintenance.

You see, in Ryan's world, there are hundreds of different procedures, each with a unique identifier such as FNACL0023 or ADUSR0012, all which are stored in a database table named "RawQuery", which, for ease of maintenance, contained the actual SQL queries to be performed. In order to call one of these queries, you'd need to call one of several actual stored procedures — Query0(), Query1(), Query2(), Query4(), Query12(), etc. — depending on the number of "parameters" needed by the query.

So, let's say you wanted to run a query. First, you'd look it up in the RawQuery table as follows.

+-----------+------------------------------------------+
| PROC_ID   |  PROC_QUERY_SQL                          |
+-----------+------------------------------------------+
| FNACL0023 | SELECT ... FROM ... WHERE ACCT_ID = %1%  |
|           |      AND STATUS_CD = '%2%' ORDER BY %3%  |
+-----------+------------------------------------------+

Then, all you had to do was the following:

CALL Query3('FNACL0023','882','ACTIVE','ORDER_DT');

It made for perfectly opaque coding, but the DBA was happy because he could easily update and add queries. Of course, the real problem was that queries were jealously and closely held by the DBA, and despite the "ease of maintenance", it was nearly impossible to convince them to make changes or add new ones.

The DBA ruthlessly audited each new release, forcing the use of stored procedures on every call. Since the manager was a former DBA himself, he always sided with the DBA.

But one day, an enterprising developer discovered a wonderful workaround in the form of query SPTST0001. It was a test query that the DBA used regularly and took only a single parameter: a table name.

+-----------+---------------------+
| PROC_ID   |  PROC_QUERY_SQL     |
+-----------+---------------------+
| SPTST0001 | SELECT * FROM %1%   |
+-----------+---------------------+

Immediately, developers changed their coding style to.

CALL Query3('SPTST0001', '(SELECT a,b,c FROM x,y,z WHERE d=1 AND e='test' ORDER BY z.c)');

All it took was an extra set of parenthesis, and a table name magically became usable as a full SQL query.

The DBA was aware of the workaround, but didn't seem to mind. So long as they were using the stored procedures as required, he didn't care what was actually in them. And besides, the developers finally stopped pestering them to add new queries.

[Advertisement] BuildMaster allows you to create a self-service release management platform that allows different teams to manage their applications. Explore how!