Thanks for the reply grasshopper !

What this is, is a product management system, and not the order system.

What I need to do while managing the products is do a insert into Production.utbProductUtbProductOption

The code below shows how I do inserts and updates on the Production.utbProductUtbProductOption table

---> Pass a list of products(Which become a product's options) in the sproc below

ALTER PROC [dbo].[uspUtbProduct_Options_Update_Ext]

@List XML,

@ProductID INT,

@UpdateFalse0InsertTrue1 BIT

AS

SET NOCOUNT ON;

DECLARE @ProductOptionIDs INT, @Cnt INT, @ProductOptionID INT;

DECLARE @TableVar table

(ID int identity(1,1) PRIMARY KEY,

ProductID INT NOT NULL

)

INSERT INTO @TableVar(ProductID)

SELECT ProductID FROM [SplitList](@List);

SELECT @Cnt = LEN(ProductID) FROM [SplitList](@List)

WHILE(@Cnt > 0)

BEGIN

SELECT @ProductOptionID = ProductID FROM @TableVar WHERE ID = (@Cnt);

IF(@UpdateFalse0InsertTrue1 = 0)

BEGIN

UPDATE Production.utbProductUtbProductOption

SET ProductOptionID = @ProductOptionID

WHERE Production.utbProductUtbProductOption.ProductID = @ProductID

END

IF(@UpdateFalse0InsertTrue1 = 1)

BEGIN

INSERT INTO [Production].[utbProductUtbProductOption]

([ProductID]

,[ProductOptionID])

VALUES

(@ProductID

,@ProductOptionID)

END

SET @Cnt = @Cnt-1

END

SET NOCOUNT OFF;

Here is where I unwrap the list

ALTER FUNCTION [dbo].[SplitList]

(

@list AS XML

)

RETURNS TABLE

AS

RETURN

(

SELECT tempTable.item.value('.', 'VARCHAR(MAX)') AS ProductID

FROM @list.nodes('list/item') tempTable(item)

);

So I have one product that has many options(Which is the list of products passed in)

----

Now what I need to do is select all the options that belong to a product

--And the most important thing that I need, and is what I am after in this post is : I need to query and pull all options from the utbProductUtbProductOption table WHERE productID != to a @ProductID.

And I can not get this going yet.

I know this sounds easy but there is something somewhere that is not allowing me to get this to work. I am always ending up with no results.

Please ask any questions and I'll answer soon as possible.

Erik