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?
 
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
 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.