Sergey

I put some comments in the code.

sampefreqs table - a table that says how often to pick a sample of a load based on the type of product (sortcode) that is being delived

sortcodes table - a table of all the possible sortcodes available.

/*
This procedure retrieves all the possible sortcode combinations based on
how the user has input the records in the samplefreqs table.  If there is
A "*" in a row in the sortcode column then all sortcodes in
the sortcodes table are valid for that brand.  Otherwise, only the
sortcodes in the samplefreqs table are valid. The values declared 
below are used as the default value for all the rest of the sortcode 
records that are not in the samplefreqs table.    

*/

create procedure sp_getfreq(in @brandid integer, in @rcode char(2))

result (sortcode char(1), descrp char(30), siteno char(2),
  freqcode char(2),frequency smallint, firstfreq smallint,
   numfirstloads smallint, checkscale tinyint)

begin
  declare @freqcode      char(2) ;
  declare @frequency     smallint ;
  declare @firstFreq     smallint ;
  declare @numfirstloads smallint ;
  declare @checkscale    smallint ;

/* this gets the values from the samplefreq table if there is
   a sortcode that has a "*" as the value. If there are no matching records,
   then the varable declared above will be null.  I could have used 
   select count(*) but that would have ment that I would have to do a 
   second select if count(*) > 0. 
*/
  select
    freqcode,
    frequency,
    firstFreq,
    numfirstloads,
    checkscale
    into @freqcode,
    @frequency,
    @firstFreq,
    @numfirstloads,
    @checkscale
  from samplefreqs
  where brandid = @brandid
     and rcode = @rcode
     and sortcode = '*'
  ;
/* if the varables are not null then do the following query.
   The first part of the query gets all the rows in the samplefreqs
   table for this brandid.  The second part of the query gets all
   the rows from the sortcodes table that are not in the samplefreqs
   table.
*/
  if @freqcode is not null then
    select s.sortcode,
    s.descrp,
    s.siteno,
    sf.freqcode,
    sf.frequency,
    sf.firstfreq,
    sf.numfirstloads,
    sf.checkscale
    from  samplefreqs sf join sortcodes s
    on sf.sortcode = s.sortcode
     and sf.rcode = s.rcode
    where sf.brandid = @brandid
    and sf.rcode = @rcode
    union 
    select s.sortcode,
    s.descrp,
    s.siteno,
    @freqcode as freqcode,
    @frequency as frequency,
    @firstFreq as firstfreq,
    @numfirstloads as numfirstloads,
    @checkscale as checkscale
    from  sortcodes s
    where not exists (select * from samplefreqs sf where sf.sortcode = s.sortcode
     and sf.rcode = s.rcode
     and sf.brandid = @brandid )
     and s.rcode = @rcode
  else
    // otherwise only get the records in the samplefreq table.
    select s.sortcode,
    s.descrp,
    s.siteno,
    sf.freqcode,
    sf.frequency,
    sf.firstfreq,
    sf.numfirstloads,
    sf.checkscale
    from  samplefreqs sf join sortcodes s
    on sf.sortcode = s.sortcode
     and sf.rcode = s.rcode
    where sf.brandid = @brandid
     and sf.rcode = @rcode
  end if ;
end

>>Sorry Sergey for taking so long. I have just today been able to get back to this problem. Here is a stored procedure that I wrote to do what I need. My question is if there is an easier or better way. I no longer have a requirement using a "*" in siteno.
>>
>< snip >
>
>I'm sorry, but I still don't understand what your code is doing or suppose to do.