September 29, 2005 at 8:59 am
Out of curiosity, is their a rule of thumb in working with a stored procedure when you have 20 or more insert statements? Is it acceptable to have one gigantic procedure? all it is is inserts. no cursors nothing. The one thing I would like to do is possibly raise errors in the procedure to see where it failed. Maybe write the last step completed out to a table?
September 29, 2005 at 9:40 am
Its a matter of preference, and also depends.
I always have 1 insert for each table.
Then I build a "Wapper" procedure that calls the individual insert procs in the order required.
The individual procedures can be reused all over the place.
Whatever gets the job done though
September 29, 2005 at 10:13 am
Thw ay I tend to approach this type of issue is to decide whether or not:
a) The statements are related in some way. E.g Do they insert into the same table or do they perform the same job, like creating a dimension table for product. If they fit that description, I will put them togther,
b) Will I always need to run the whole batch of inserts. If you have a lot of variables declared and want to run the 8th and 15th and 20th insert statements, it can get very tricky. In this case I keep them apart.
c) Do they reference each other or have dependency on each other. If so, put them together.
Hope these 3 ideas help you out.
September 29, 2005 at 2:42 pm
Ah yes, the wascally wapper pwocedure.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply