Ease the Burden of Overloading

A while back I said that function overloading is marvelous. I haven’t changed my mind. The same is true of overloaded stored procedures. The only negatives to overloading are a couple of annoyances that the system requires to maintain order. Fortunately, those annoyances are easily circumvented. Here’s what and how.

Assume a table of items. Each item is identified by a five-digit ID code for internal use only. Customers use an alternate catalog ID to refer to items.

create table Items
   (    ID                    dec (5,0),
        Catalog_ID            char(6),
        MaterialThisLevel     dec (5,2),
        LaborThisLevel        dec (5,2),
        OverheadThisLevel     dec (5,2),
        MaterialLowerLevels   dec (5,2),
        LaborLowerLevels      dec (5,2),
        OverheadLowerLevels   dec (5,2),
     primary key (ID)
   )

Notice that there are six cost columns (fields): material, labor and overhead (also called burden) costs for the item itself and those same costs for the items that make up this item. The total cost of an item is the sum of the six costs.

Let’s write a function that retrieves the cost of an item, given an ID. In fact, while we’re at it, let’s write a second function that retrieves the cost of an item, given a catalog number.

create function RetrieveCost
   ( p_ID dec(5))  returns dec(7,2)
returns null on null input
begin
   declare v_Cost   dec(7,2);
   select (MaterialThisLevel + LaborThisLevel +
           OverheadThisLevel + MaterialLowerLevels +
           LaborLowerLevels  + OverheadLowerLevels)
     into v_Cost
     from Items
    where ID = p_ID;
    return v_Cost;
end

create function RetrieveCost
   ( p_Catalog_ID char(6))  returns dec(7,2)
returns null on null input
begin
   declare v_Cost   dec(7,2);
   select (MaterialThisLevel + LaborThisLevel +
           OverheadThisLevel + MaterialLowerLevels +
           LaborLowerLevels  + OverheadLowerLevels)
     into v_Cost
     from Items
    where Catalog_ID = p_Catalog_ID;
    return v_Cost;
end

Now we have two functions of the same name, and we can call them to retrieve costs by ID or catalog ID from any query.

select . . . RetrieveCost (x.ItemID) . . . 
  from Sales as x

select . . . RetrieveCost (x.Catalog_ID) . . .
  from Orders as x

So far so good. Now let’s look at the system catalog to see what’s going on behind the scenes.

select routine_name, specific_name, external_name
  from sysfuncs where routine_name = 'RETRIEVECOST'

drop function retrievecost

drop function retrievecost (dec(5))

create function RetrieveCost
   ( p_ID dec(5))  returns dec(7,2)
   specific RtvCostID
   returns null on null input
begin
   declare v_Cost   dec(7,2);
   select (MaterialThisLevel + LaborThisLevel +
           OverheadThisLevel + MaterialLowerLevels +
           LaborLowerLevels  + OverheadLowerLevels)
     into v_Cost
     from Items
    where ID = p_ID;
    return v_Cost;
end

create function RetrieveCost
   ( p_Catalog_ID char(6))  returns dec(7,2)
   specific RtvCostCat
   returns null on null input
begin
   declare v_Cost   dec(7,2);
   select (MaterialThisLevel + LaborThisLevel +
           OverheadThisLevel + MaterialLowerLevels +
           LaborLowerLevels  + OverheadLowerLevels)
     into v_Cost
     from Items
    where Catalog_ID = p_Catalog_ID;
    return v_Cost;
end

comment on specific function rtvcostid
   is 'Retrieve item cost by ID'

alter specific function rtvcostid
   called on null input          

drop specific function rtvcostid