views:

96

answers:

2

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.

+4  A: 

It looks like the following link may be of use to you:

http://www.sommarskog.se/share_data.html

Kragen
Unfortunately, that link basically told me "there isn't really anything you haven't thought of". So I'm stuck with the inelegnace :(
Dems
@Dems: That's right, there is no better option. What you can consider is some code generation tool (even simple #include tricks might work, or XML+XSLT for something more complex) to generate the procedures so that you can easily maintain them.
Remus Rusanu
The database is a stand-alone entity. It is designed to agnostic of the platform interacting with it, and with permissions specifically controlled such that the app could not do harm even if it wanted to. I am a very strong believer in such approaches, so #include would be out. It's similar to the dynamic SQL aption anyway, and the overhead there is significant due to the complexity of some of the queries.
Dems
+1  A: 

Sigh, this is going to be crufty without SQL 2008. Oh, and BTW, Erland's post (above) is where I'd go for a list of all of the options...

Dave Markle
What is "crufty"?
Dems
:) It means sort of dirty and inelegant.
Dave Markle