Hi,
I've had this problem a few times and solved it the same way. But it's not pretty and I was wondering if anyone knew of anything a bit more elegant...
The Basic Aim:
- I have a piece of code that is common through-out many Stored Procedures.
- For maintenance purposes I like modularity.
- So I want to put that common code in it's own Stored Procedure.
- All the other stored procedures call that one.
- Hey presto, modular, re-usable, maintainable code.
The Complicating Issue:
- The common code generates two record sets / tables
- A Stored Proc or Function can't return two tables to a Stored Proc
- Generating the two tables in separate functions would significantly decrease performance
(This would cause significant redundant recalculation)
The Solutions Used Historically:
1> Create #Temp Tables in the outer SP
- The central code inserts into the #temp tables
- Allows any number of data sets to be returned
- If new columns are added to the data, every outer SP to be revised as well.
2> Create real tables to insert the data into.
- If new columns are added to the data, only one set of table definitions to change.
- Needs "Session IDs" to differentiate between 'my data' and another processes data.
- Errors, etc, can leave old data in the table, requiring a nightly clean up process.
- Clutters up the database.
The first option gets closer to the modularised goal. Logic changes only need to go in one place. But additional columns, etc, require identifying every bit of code that uses the SP and updating the #temp table definitions.
The second option makes that much more elegant, but introduces a whole raft of new considerations which kill the elegance off again.
Using only SQL Server 2005, is there a better option? (Working in SQL Server 2000 would also be a bonus.)
Cheers, Mat.