By Kalman Toth, M.Phil., M.Phil., MCDBA

December 9, 2004

Principal Trainer at: http://www.sqlusa.com/

When coding queries and stored procedures, frequently it seems to be simpler and faster to hard code literals. For example:

Select "Low Volume" from Sales where Quantity < 5

Select "Medium Volume" from Sales where Quantity >=5 and Quantity <20

Select "High Volume" from Sales where Quantity >=20

Often the CASE statement is used to do such a hard-wiring.

The disadvantage of this approach is the application is not table-driven anymore, rather a combination of table driven and code driven.

If this is in a stored procedure, the situation is somewhat better than being in imbedded code. In a stored procedure, at least it is visible in the database.

There are two big disadvantages of hard-wiring:

To change the literals requires programming change and recompilation
The literals are not available for reporting and data warehousing
To make the example table driven, we have to return to data modelling. We need a SalesVolume table.

Create table SalesVolume (

SalesVolumeID int,

VolumeLevel char (20),

Low int,

High int)

Populate it:

Insert SalesVolume (VolumeLevel, Low, High) values (‘Low Volume’, 0, 4)

Insert SalesVolume (VolumeLevel, Low, High) values (‘Medium Volume’, 5, 19)

Insert SalesVolume (VolumeLevel, Low, High) values (‘High Volume’, 20, 99999999)

Note that the last High value must be higher than expected high value in the given business.

To do the select we have to join the two tables

Select b.VolumeLevel from Sales a, SalesVolume b

Where a.Quantity >= b.Low and a.Quantity <= b.High