Stored Procedure to Update a Many - to - Many Relationship

I am writing a stored procedure to update a products placements. I have a legacy database that contains a product table, a placements table, and a ProductsInPlacements table.  The productsInPlacements table contains a productID as a FK, and a placementsID as a FK.  
What is the best practice for updating this type of relationship?  Currently I am updating, I first delete all the records from ProductsInPlacements that match a given product ID, then I loop calling an update procedure that take the productId and placementID and updates the ProductsInPlacements table.  

This requires two stored procedures, one to delete all the records and another to update the table.  Is this the best way?  

I wrap everything in a transaction in case something bad happens during the update. I am using SQL server.  

Any guidance would be greatly appreciated.    
shanemayAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Guy Hengel [angelIII / a3]Billing EngineerCommented:
you might be interested to know how to pass a list of values to a procedure:
http://www.experts-exchange.com/articles/Database/Miscellaneous/delimited-list-as-parameter-what-are-the-options.html

bases on such a list, transformed into a "table", you could selectively add new ones, and delete those no longer in the list.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Guy Hengel [angelIII / a3]Billing EngineerCommented:
sample code, proc to be called like this:

exec yourproc 1111,  '1,3,4'
create procedure yourproc @parent int, @child_list varchar(1000)
as 
begin
  declare @children table ( id int )
  insert into @children (id) select value from dbo.ParmsToList(@child_list, ',')

  delete your_relation_table
   where parent_id = @parent
     and child_id not in ( select id from @children where id is not null )

  insert into your_relation_table (parent_id, child_id)
   select @parent, c.id
     from @children c
     left join your_relation_table r
       on r.parent_id = @parent
      and r.child_id = c.id
   where r.child_id is null

end

Open in new window

0
shanemayAuthor Commented:
Thank you for your time and consideration on my behalf.  
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.