Did you tried to use a temporary table or recursion? I am sorry that I cannot offer you more details since I don't know the specifics, but here is how I'd wrote it: (1) Recursion. This assumes that your region table cannot find a loop (from a region parent you cannot get back to the same region parent). Anyway, if this happens, select into a temporary table all the regions you can find starting with a parent, eliminate duplicates and you'll get a table containing the parent region candidates (either our passed ID or an ancestor), and also you can create another temporary table with all the details you need joining the #parents with #products (or @productID, if only a product interests you).
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
(2) Temporary tables.
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 paren
