I'm going to explain as clearly as possible:
I have two tables:
Relationships(relation_id, table1, table2)
Relationfields(relation_id, field1, field2)
In Relationships, relation_id is the primary key
In Relationfields, relation_id is the foreign key
I have a front-end interface that allows the user to add records to
Relationships and Relationfields as followed:
The user selects a table1 and table2 values from listboxes. These are
real table names from sys.objects, so then the user can select fields
of these tables on which he wants to create a JOIN.
Anyway, I can easily insert the table1 and table2 into Relationships
(relation_id is an auto-increment). Then I need to get the relation_id
of this new Relationship (easy since I know which values I've inserted
and table1-table2 associations are unique.
Now the PROBLEM :
I need to insert into Relationfields all the fields selectioned by the
user for each of the two tables . But the user might have selected
several fields from table1 and table2, so I need to pass A LIST
PARAMETER to my Stored Procedure as I don't know how many values of
field1 and field2 there is going to be.
I hope this is clear enough. Is it possible to achieve what I want ?
Should I pass an entire concatenated string with values separated by
comma or whatever and then decrypt it in the stored procedure ?
ThxCould anyone help please ?|||If you're asking how to pass a list into a procedure as a parameter,
then see here:
http://www.sommarskog.se/arrays-in-sql.html
If that doesn't help, I suggest you post CREATE TABLE and INSERT
statements to clarify exactly what you need:
http://www.aspfaq.com/etiquette.asp?id=5006
Simon|||thanks. I think the first link should help me doing what I want.
Regards|||Why are you putting metadata in your schema, in total violation of the
basic principles of a relational database? Why don't you know the
differences between rows and records, fields and columns, data and
metadata?
You can kludge this with a lot of dynamic SQL that will run like cold
glue, have no data integrity and be unmaintainable. Someone will
probably help you do this. Please read a few books before you destroy
your employers data.|||The best way to pass multiple-row data into a stored procedure is using
OPENXML, any other method is a kludge.|||I put meta data in my shema because my employer pays me to do so... I'm
developping an administration tool for maintaining dynamic metadata of
a website.
Now I'm not sure in what way your comment is of any use to me...|||Ellen K > Thanks I'll look at this in the future. But I've found a
better way this time to avoid passing multiple-row data to my stored
procedure:)
Thx!
