irascian wrote:putting all the logic into a stored proc that gets called once rather than artificially using multiple stored proc invocations can make for much better performance.
I do try to avoid multiple sproc calls for what is logically a single operation.
What I willt typically do is chain my stored procedures. Let's say I have a Customer table and an Order table. If I want to be able to get a single customer and all its orders (where I don't want to use a join since I don't need all the customer's information on every row), I'd have a GetOrdersForCustomer sproc, and a GetCustomer sproc that would first select the customer and then run "exec GetOrdersForCustomer @id".
Then the DAL may figure out how to put those two results sets together. But both results are returned from a single call to the DB.