I've written a stored procedure in Interbase which takes four input
parameters and returns a multiple row result set.

CREATE PROCEDURE pSiteBrowse(pSiteRegion Char(2),
                             pSiteArea   Char(2),
                             pSiteType   Char(2),
                             pCatchment  Char(4))
   RETURNS (
        fSiteRef        Char(14) )

As you would expect the four input parameters are used in the WHERE clause.

WHERE ( tSites.fSiteRegion LIKE :pSiteRegion AND
                      tSites.fSiteArea LIKE :pSiteArea AND
                      tSites.fSiteType LIKE :pSiteType AND
                      tSites.fCatchment LIKE :pCatchment )

Is it somehow possible to allow the user only to specify say two of the
parameters and still return a result set ?  As you can see i've tried using
the LIKE keyword but have had problems trying to pass an '%' in quotes to
represent a null parameter.

The only way I can think of now doing this is to replicate the stored
procedure four times ( with shortened where clauses) checking the
parameters to see if they are NULL.  Is there a better way ?

Hope this makes some sense.  Any replies greatly received

Thanks very much

Stef Fiorentini

CREATE PROCEDURE pSiteBrowse(
                             pQueryType SmallInt,
                              pSiteRegion Char(2),
                             pSiteArea   Char(2),
                             pSiteType   Char(2),
                             pCatchment  Char(4))
   RETURNS (
        fSiteRef        Char(14) )
AS BEGIN
IF  (:QUERYTYPE = 1) THEN
 DO
  BEGIN
    SELECT fSileRef FROM tSites
    WHERE
       fSiteRegion LIKE :pSiteRegion AND
       fSiteArea LIKE :pSiteArea
     INTO
      :fSiteRef;
 END
  IF  (:QUERYTYPE = 2) THEN
 DO
  BEGIN
    SELECT fSileRef FROM tSites
    WHERE
      fSiteRegion LIKE :pSiteRegion AND
      fSiteArea LIKE :pSiteArea AND
      fSiteType LIKE :pSiteType AND
      fCatchment LIKE :pCatchment
     INTO
      :fSiteRef;
 END
END

AND SO ON..........................
Will Wilson
CWILSON...@AOL.COM