Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Update a many-to-many join table

Posted on 2004-07-31
14
Medium Priority
?
535 Views
Last Modified: 2012-05-05
Hi,

I'm up against a simple difficulty I didn't think of until I tried to do it. I've got a many-to-many relationship which is implemented with a "join" table (two columns, FKs from the two linked tables). I've got my client-side checkbox list working, but what's the best way to update the table to what the user has selected (in a stored procedure)? This must be a common scenario.

The thought I had was to pass two params;
@table1_Id (self-explanatory), and
@selectedValues -a comma-separated varchar of all the values selected in the checkbox list, i.e. all the table2 Ids.

But I don't know how to update the join table in TSQL with this. Could someone provide a real solution to this outline solution, or even beter tell me what is good practice for this scenario?

Thank you.
0
Comment
Question by:sumo_the_cat
  • 8
  • 6
14 Comments
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 11685072
>>The thought I had was to pass two params;
@table1_Id (self-explanatory), and
@selectedValues -a comma-separated varchar of all the values selected in the checkbox list, i.e. all the table2 Ids.<<

This is the way that most people do it.  Search for array or delimited list in the Topic Area and you will find many solutions.

Since my company is committed to Xml, we have opted for a different alternative using OpenXml.  One advantage of this solution is that you do not have to parse a delimited list or have multiple inserts, but rather just one single insert for all the multiple values.  

Let me know if you need examples using either option.
0
 
LVL 2

Author Comment

by:sumo_the_cat
ID: 11685113
Yes, I'd love to learn to do this with XML. The thing which made me ask the q was that I didn't want to do multiple inserts (ugly). I'm using ASP.NET as the client application for this database, so it ought to be able to generate some XML from the CheckBoxList control...?
Peter.
0
 
LVL 2

Author Comment

by:sumo_the_cat
ID: 11685156
I'm looking up OpenXml on the net, but could you give me a syntax example for this scenario? The other problem is how to generate the XML on the client. I can't do either of these things!
0
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 
LVL 2

Author Comment

by:sumo_the_cat
ID: 11685177
Could I generate the XML manually, like this? Just loop through the checkBoxList and add a new <item> each time I come accross a selected checkbox?
<root>
      <item>
            <id>x</id>
      </item>
</root>
0
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 2000 total points
ID: 11685345
>>Just loop through the checkBoxList and add a new <item> each time I come accross a selected checkbox?<<
You could certainly do it that way.  In any case, supposing your Xml looks like this:
<IDs>
   <ID>123456</ID>
   <ID>654321</ID>
   <ID>111111</ID>
   <ID>666666</ID>
</ID

And your Stored Procedure looked like this (untested):

Create Procedure usp_Test
                    @IDList text
AS

Declare @idoc int

EXEC sp_xml_preparedocument @idoc OUTPUT, @IDList

--INSERT     Table2 (ID)                        -- Uncomment this line when you are getting the right results and ready to insert.
Select       ID
From      OpenXML (@idoc, 'IDs\ID', 2) With (ID  int '.')

EXEC sp_xml_removedocument @idoc

Than you would execute the Stored Procedure as follows:

exec usp_test '<IDs>
                            <ID>123456</ID>
                            <ID>654321</ID>
                            <ID>111111</ID>
                            <ID>666666</ID>
                      </ID'

As you can see this very powerful as you can now modify the Xml to include other values that you need to pass.
0
 
LVL 2

Author Comment

by:sumo_the_cat
ID: 11685514
Great! Thanks.
My generated XML looks like this:
<values><id>1</id><id>3</id><id>7</id></values>

The relevant part of my sproc looks like this:
---------------------------------------------------------------
Declare @idoc int

EXEC sp_xml_preparedocument @idoc OUTPUT, @selectedValues

INSERT INTO dbo.[JoinTable] (Table2_Id)                        
Select       [id]
From     OpenXML (@idoc, 'values\id', 2) With ([id]  int '.')

EXEC sp_xml_removedocument @idoc
---------------------------------------------------------------
but I'm getting this SQL error:
XML parsing error: Unexpected character in query string. values-->\<--id

Pete.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 11685543
Change this:
From     OpenXML (@idoc, 'values\id', 2) With ([id]  int '.')

To:
From     OpenXML (@idoc, 'values/id', 2) With ([id]  int '.')
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 11685545
P.S. My code was wrong.
0
 
LVL 2

Author Comment

by:sumo_the_cat
ID: 11685547
Should be forward slash with leading slash:
---------------------------------------------------------------
From     OpenXML (@idoc, '/values/id', 2) With ([id]  int '.')
---------------------------------------------------------------
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 11685558
You do not need the leading forward slash, on the other hand it does not hurt.
0
 
LVL 2

Author Comment

by:sumo_the_cat
ID: 11685565
Jinks! I'm now trying to work out how to add the other column.
0
 
LVL 2

Author Comment

by:sumo_the_cat
ID: 11685579
Got it - perfect! Thank you so much. I've really learnt something useful and elegant.
Peter.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 11685591
>> I'm now trying to work out how to add the other column.<<
Feel free to post all your input parameters for this stored procedure, the actions to be taken (Insert and table structure) and I can make suggestions.
0
 
LVL 2

Author Comment

by:sumo_the_cat
ID: 11685645
Nah, I was just being thick. Thanks again!
0

Featured Post

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

886 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question