在SQL Server 2005中寻求模块化——向存储过程返回多个记录集

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)

复杂的问题:-公共代码生成两个记录集/表—存储的Proc或函数不能将两个表返回到存储的Proc—在单独的函数中生成两个表将显著降低性能(这将导致显著的冗余重新计算)

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.

1>在外部SP中创建#Temp表——将中心代码插入到#Temp表中——允许返回任意数量的数据集——如果向数据中添加新列，也可以修改每个外部SP。

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.

>创建真正的表来插入数据。-如果向数据添加新列，则只需要更改一组表定义。-需要“会话id”来区分“我的数据”和另一个处理数据。-错误，等等，可以将旧数据留在表中，需要每晚清理流程。-数据库混乱。

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.

第一种选择更接近模块化目标。逻辑改变只需要放在一个地方。但是其他列等需要标识使用SP的每一个代码位并更新#temp表定义。

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.)

只使用SQL Server 2005，有更好的选择吗?(在SQL Server 2000中工作也是一种奖励。)

Cheers, Mat.

干杯,垫。

2 个解决方案

#1

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

以下链接可能对您有用:

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

#2

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...

叹气，如果没有SQL 2008，这将是多么的残酷。顺便说一句，Erland的post(上图)是我列出所有选项的地方。

#1