Hi TSQL experts!
I have two tables, one with a list of product options (table name ProductOptions), and another with a list of product options selected for a specific product (table name ProductOptionMapping). Users can choose options for their products that already exist in ProductOptions, or they can add their own new option. I want to make sure that duplicate ProductOptions don't get created when they enter a new option.
I am trying to create a stored procedure that will check if the option they entered already exists in the ProductOptions, and if not add it. Then it will insert the found (or newly inserted) ProductOptionID into the ProductOptionMapping.
Here are how the tables are organized (simplified for this example):
ProductOptionID (int, pk)
ProductID (int, pk, fk)
ProductOptionID (int, pk, fk)
Of course there's a Products table too...
I have attached some pieced together SQL, though it's obviously not complete or correct. Any help in getting this to work is greatly appreciated.
IF NOT EXISTS (SELECT ProductOptionID FROM ProductOptions WHERE ProductOptionName = @ProductOptionName)
INSERT INTO ProductOptions (ProductOptionName ) VALUES (@ProductOptionName);
INSERT INTO ProductOptionMapping(ProductID,ProductOptionID) VALUES(@ProductID,SCOPE_IDENTITY())
INSERT INTO ProductOptionMapping(ProductID,ProductOptionID) VALUES(@ProductID,[Found ProductOptionID here])