calling multiple stored procedures from a stored procedure

Hi.

I'm quite sure that it's just my search methods that suck, but I can't find any good examples of how to call multiple stored procedures from a stored procedure.

The thing is that I have a whole bunch of tables, each with auto generated stored procedures for get by id, get all, insert, delete, update...
Since I want avoid multiple calls to the database to fill my business objects I thought I'd make a sp that gathers data from multiple tables, using the standard get sp's...

the structure looks like this

base (baseID, baseCol1, baseCol2)
base_elementContainer (base_elementContainerID, baseID, elementContainerID)
elementContainer (elementContainerID, elementContainerCol1, elementContainerCol2)
elementContainer_element (elementContainer_elementID, elementContainerID, elementID)
element (elementID, elementCol1, elementCol2)
element_objectOne (element_objectOneID, elementID, objectOneID)
objectOne (objectOndeID, objectOneCol1, objectOneCol2)

element_objectTwo (element_objectTwoID, elementID, objectTwoID)
objectTwo (objectTwoID, objectTwoCol1, objectTwoCol2)

and what I need to do is to fetch all objectOne and objectTwo by baseID... and collect a few things on the way...

would be happy to get a few quick pointers or a link to some good example codes or a tutorial...

thanks...

You can get results from multiple procs into local tables. Refer to this post: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1016268&SiteID=1