Database

Create Procedure sp_ProductRegionalDetails 
  @productID int,
  @regionID  int
As
  Declare @lRetVal int /* return status: 0 - success (found), 1 - fail(not found), negative value (error) */
  /* Try to get the details for product, of from parent etc. */

  Select @lRetVal = 1 /* assume not found */

  Declare @fExitLoop Bit
  Select @fExitLoop = 0

  Declare @LoopProductID int
  Declare @LoopRegionID  int

  Select @LoopProductID = @productID, @LoopRegionID = @regionID
  While (@fExitLoop = 0)
  Begin
    Select [... Product regional details ...] From [... Tables, Joins ...] Where 
      ProductIdField = @LoopProductID And 
      RegionIdField  = @LoopRegionID And
      And [... additional conditions, etc ...]

      If [The field interesting for me are retrieved]
      Begin
        Select @fExitLoop = 1
        Select @lRetVal = 0 /* found */
      End
      Else
      Begin
        Select @LoopRegionID = Exec sp_ParentRegion @LoopRegionID 
        If @LoopRegionID Is Null
        Begin
          Select @fExitLoop = 1 /* no parent, exit loop */
        End
      End
  End

Return @lRetVal

Create Procedure sp_ProductRegionalDetails 
  @productID int,
  @regionID  int
As
  Declare @lRetVal int /* return status: 0 - success (found), 1 - fail(not found), negative value (error) */
  /* Try to get the details for product, of from parent etc. */

  Select @lRetVal = 1 /* assume not found */

  [Create a temporary table containing all regions]
  -- Create Table #regions (regionID int, ToSelect bit default(0) ) - for example
  [Populate this table from our starting point: @regionID - the table will be populated in the natural order, i.e. starts with our regionID, if will find something then return, if not advance to the next parent etc.]
  [Eliminate duplicated regionID]
  [Query temporary table by joining with @productID]
  [Retrieve results and mark in the temporary table ToSelect = 1 where informations you need were found]
  [For the first entry in the temporary table with ToSelect = 1 get this row; this is our first row]
  [If all the rows in temporary table have ToSelect = 0 --> no results found and return failure]
  [If at least one row has ToSelect = 1, return the results associated with this regionID]
Return @lRetVal

VOID GetRegionInfo(DWORD *pdwResultCount, LP_PRODUCTRESULTS *ppResult)
{
   // format a name of temporary table using computer name and tick count, something like:
   // #[TableNamePattern][ComputerName][TickCount]
   CreateTemporaryTable(szTempTableName);
   CallSP(szStoredProc, szSPName, pParams);
   SelectResultsFromTemporaryTable(pdwResultCount, ppResult);
   DropTemporaryTable(szTempTableName);
}