Database Opinion: A case for canned SQL

Like a Phoenix, the dynamic SQL versus canned procedures and user functions argument has resurfaced on the SQL newsgroups. Many of the proponents of the dynamic argument are web or Access developers, or developers of some other front end. Developers in the canned camp tend to be DBAs or developers with some depth of knowledge in stored procedures and functions.

My characterization has, of course, loaded the dice in favor of the latter group, of which I consider myself a member. After exploring some of my reasons and experiences, I’ll cite a simple example to illustrate my conclusion that canned SQL is always the better option.

How they differ

Many of the forms of dynamic SQL I use in my web and client applications enable the user to specify complex selection combinations. In fact, there might be 20 such controls on the selection form.

Given this complexity, I have no choice but to examine each control in the front end. If it is not null, then I create a clause in the SELECT statement that corresponds to the column and the value specified. I build up the statement control by control, then issue it.

Conversely, canned SQL is available to any and all front ends that need it, without duplicating the logic in each front end. It also runs more quickly and precludes SQL injection almost entirely.

I admit that in some instances there is no alternative but to write dynamic SQL statements and send them to the back end for execution. But in my experience, these instances are few and far between.

Code is worth a thousand words

Below is an example that uses only three controls, but should be sufficient to illustrate my point. It also reveals a cool syntax trick that you can apply to your own projects.

Suppose your application presents a form with three lists, from which the user can select a maximum of one item from each. Let’s also suppose that this form talks to a single table-or view or UDF-with columns A, B and C.

The possible combinations of the three columns are:

A	all As.
AB	all specified As that are also Bs.
ABC	all specified As that are also Bs and also Cs.
B	all Bs.
BC	all specified Bs that are also Cs.
(BA	same as AB above.)
C	all Cs.
CA	all Cs that are also As.
(CB	same as BC above.)

To construct the dynamic SQL necessary to accommodate these variations, you would examine each control, determine if it is not NULL, and gradually build up the SQL statement.

My counter proposal is a single procedure or user function that can handle all combinations of these values. I have chosen to write a user function because table user functions can be invoked as part of a view, stored procedure, or user function. But it could just as easily be a stored procedure.

Here is the code to create the sample table:

Here are a few sample rows:

Here is the code to create the table function:

The trick in this function is the comparison between the expected parameters and themselves. Think about the logic for a moment before proceeding.

Finally, here are several sample calls:

Conclusion

My simple example uses only three arguments and assumes exact matches on each specified argument. But it should be obvious that you can do the same thing with 20 arguments. With a little more work, the function can be modified to handle LIKE arguments and their converse as well.

My point is not that this approach will cover every possible situation, but that it will cover many situations in which you might have used dynamic SQL instead. By now, the gains should be obvious.