real ****er of an SQL query

Nick Blumhardt

...if you'll excuse the topic line ...:-O what a day- sifted through miles of docs, to still be stuck on this one: I have a stored procedure sp_ProductRegionalDetails wich takes 2 arguments- @productID and @regionID. This will return a row containing the details of that product, localized (brand etc.) to that region. The thing is, if it does not find an entry for that region, it will search the wider 'parent' region of @regionID, and so-on. Works a treat. I now need a rowset containing all of the products (their details) in a region. I have a column of distinct ProductIDs and a single RegionID. I need to call sp_ProductRegionDetails with each ProductID and the RegionID, and append the results somehow to the others... Did I explain my problem..? thanks for any help Nick

Nick Blumhardt

yeah baby :)

Lost User

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