updating a one-to-many DB with stored procedure

Question

updating a one-to-many DB with stored procedure

Darkmunk

Valued Member

Points: 55
More actions
September 25, 2007 at 5:21 am

#198315

I have a Normalised DB with 3 tables related to one another by ID (I understand this is the correct way to do it) so instead of having one big table with field01, field02, etc hard coded into it. it has just 'field' and another column with a sequence number and the ID column. 'ID' relates the fields and 'sequence' identifies the order for display and could be appended to a variable for looping etc.
I have a rough idea how to write and execute stored procedures but what is the technique for updating these three tables all at once (preferably). UP to 20 'fields' can be generated in the original insert. My ASP 'edit' page generates a form pre-populated with the values in the DB for the requested ID and only creates form fields where the DB field has a value (so that it can be edited).
it then sends a bunch of parameters to the SP. so the SP knows which '@field' has a value and needs updating.
I feel like I have to loop thru a recordset using the 'sequence' value and update each 'field'...
Any clues?

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Tomm Carr SSCertifiable Points: 5426 More actions · Answer 1

Maybe I just don't understand but I don't see what the problem is. Say you have an entity you have normalized across three tables, Main, Norm1 and Norm2. So your sp has a parameter list for all the values for the entity. You insert into Main first (because the others, if designed properly, will have foreign key constraints so the Main record must already exist) and then to Norm1 and Norm2 (in any order). So the proc would look kinda like this:

create proc WriteEntity (@KeyVal, @Item1, @Item2, @Item3) as
begin
insert into Main(MainID, Attr1) values (@KeyVal, @Item1);
insert into Norm1(MainID, Attr2) values (@KeyVal, @Item2);
insert into Norm2(MainID, Attr3) values (@KeyVal, @Item3);
end

This is a simple example with no additional processing or error checking but it shows the steps involved. If you've defined Main with an identity field as the primary key, you have to retrieve the value of that key after the insert into Main (using @@Identity) so you can use it in the subsequent inserts.

Tomm Carr
--
Version Normal Form -- http://groups.google.com/group/vrdbms

Darkmunk Valued Member Points: 55 More actions · Answer 2

thanks.

I think I basically don't know how to loop thru the @variables and do the insert. I'm sure it is easy as it is one of the most common requirements of any content management system - updating a DB.

Anyway, I have an expert on the case. For the sake of other newbies I will attempt to answer my own post later.

Tomm Carr SSCertifiable Points: 5426 More actions · Answer 3

Of course, if you supplied more details of the structure of the tables involved, we could supply more detailed answers. 🙂

Tomm Carr
--
Version Normal Form -- http://groups.google.com/group/vrdbms