I have an application that (unfortunately) contains a lot of its business logic is stored procedures.
我有一个应用程序(不幸的是)包含了很多业务逻辑的存储过程。
Some of these return masses of data. Occassionally the code will need a small amount of the data returned from the stored procedure. To get a single clients name, I need to call a stored procedure that returns 12 tables and 950 rows.
其中一些返回大量的数据。偶尔，代码将需要从存储过程返回的少量数据。要获得单个客户机名称，我需要调用一个存储过程，该过程返回12个表和950行。
I am not able (due to project politics) to change the existing stored procedures or create a replacement stored procedure - the original massive procedure must be called as that contains the logic to find the correct client. I can create a new procedure as long as it uses the original massive procedure.
我不能(由于项目政治)更改现有的存储过程或创建一个替换的存储过程——必须调用原始的大型过程，因为它包含查找正确客户端的逻辑。我可以创建一个新的程序，只要它使用原始的大规模程序。
Is there anyway I can get SQL server to return only a subset, (a single table, or even better a single row of a single table) of a stored procedure?
我是否可以让SQL server只返回存储过程的一个子集(一个表，甚至是一个表的一行)?
I have to support sql server 2000 +
我必须支持sql server 2000 +
3 个解决方案
#1
3
It is not possible to conditionally modify the query behaviour of a procedure whose source code you cannot change.
不可能有条件地修改过程的查询行为，因为您无法修改过程的源代码。
However, you can create a new procedure that calls the original then trims down the result. A SQL 2000 compatible way of doing this might be:
但是，您可以创建一个新的过程来调用原始过程，然后对结果进行处理。一种与SQL 2000兼容的方式可能是:
declare @OriginalResult table (
// manually declare every column that is returned in the original procedure's resultset, with the correct data types, in the correct order
)
insert into @OriginalResult execute OriginalProcedure // procedure parameters go here
select MyColumns from @OriginalResult // your joins, groups, filters etc go here
You could use a temporary table instead of a table variable. The principle is the same.
您可以使用临时表而不是表变量。原理是一样的。
You will definitely pay a performance penalty for this. However, you will only pay the penalty inside the server, you will not have to send lots of unnecessary data over the network connection to the client.
你肯定会为此付出代价的。然而，您将只支付服务器内部的罚款，您将不必通过网络连接向客户端发送大量不必要的数据。
EDIT - Other suggestions
编辑-其他建议
- Ask for permission to factor out the magic find client logic into a separate procedure. You can then write a replacement procedure that follows the "rules" instead of bypassing them.
- 请求允许将“找到客户端”逻辑分解为单独的过程。然后，您可以编写一个遵循“规则”的替换过程，而不是绕过它们。
- Ask whether support for SQL 2000 can be dropped. If the answer is yes, then you can write a CLR procedure to consume all 12 resultsets, take only the one you want, and filter it.
- 询问是否可以放弃对SQL 2000的支持。如果答案是肯定的，那么您可以编写一个CLR过程来使用所有12个resultset，只取您想要的一个，并对其进行过滤。
- Give up and call the original procedure from your client code, but find a way of measuring the performance drop, so that you can exert some influence on the decision-making backed up with hard data.
- 放弃并从客户端代码中调用原始过程，但是找到一种度量性能下降的方法，这样您就可以对硬数据支持的决策施加一些影响。
#2
1
No, you can't. A stored procedure is a single executable entity.
不,你不能。存储过程是一个可执行实体。
You have to create a new stored proc (to return what you want) or modify the current one (to branch) if you want to do this: project politics can not change real life
您必须创建一个新的存储proc(以返回您想要的)或修改当前的proc(到branch)(如果您想这样做的话):项目政治不能改变现实生活
Edit: I didn't tell you this...
编辑:我没有告诉你这个……
For every bit of data you need from the database, call the stored procedure each time and use the bit you want.
对于数据库中需要的每个数据位，每次都调用存储过程并使用所需的位。
Don't "re-use" a call to get more data and cache it. After all, this is surely the intention of your Frankenstein stored procedure to give a consistent contract between client and databases...?
不要“重用”调用来获取更多数据并缓存它。毕竟，这肯定是您的弗兰肯斯坦存储过程的意图，即在客户端和数据库之间提供一致的契约……?
#3
0
You can try to make SQL CLR stored procedure for handle all tables returned by your stored procdure and in C# code to find data you need and return what you need. But I think that is just is going to make things more complicated.
您可以尝试使用SQL CLR存储过程来处理存储procdure返回的所有表，并使用c#代码查找所需的数据并返回所需的数据。但我认为这会让事情变得更复杂。
When you fill your dataset with sored procedure which return more results sets in data set you get for each result set one DataTable.
当您用排序过程填充数据集时，它将返回数据集中的更多结果集，您将为每个结果集获得一个DataTable。
#1
3
It is not possible to conditionally modify the query behaviour of a procedure whose source code you cannot change.
不可能有条件地修改过程的查询行为，因为您无法修改过程的源代码。
However, you can create a new procedure that calls the original then trims down the result. A SQL 2000 compatible way of doing this might be:
但是，您可以创建一个新的过程来调用原始过程，然后对结果进行处理。一种与SQL 2000兼容的方式可能是:
declare @OriginalResult table (
// manually declare every column that is returned in the original procedure's resultset, with the correct data types, in the correct order
)
insert into @OriginalResult execute OriginalProcedure // procedure parameters go here
select MyColumns from @OriginalResult // your joins, groups, filters etc go here
You could use a temporary table instead of a table variable. The principle is the same.
您可以使用临时表而不是表变量。原理是一样的。
You will definitely pay a performance penalty for this. However, you will only pay the penalty inside the server, you will not have to send lots of unnecessary data over the network connection to the client.
你肯定会为此付出代价的。然而，您将只支付服务器内部的罚款，您将不必通过网络连接向客户端发送大量不必要的数据。
EDIT - Other suggestions
编辑-其他建议
- Ask for permission to factor out the magic find client logic into a separate procedure. You can then write a replacement procedure that follows the "rules" instead of bypassing them.
- 请求允许将“找到客户端”逻辑分解为单独的过程。然后，您可以编写一个遵循“规则”的替换过程，而不是绕过它们。
- Ask whether support for SQL 2000 can be dropped. If the answer is yes, then you can write a CLR procedure to consume all 12 resultsets, take only the one you want, and filter it.
- 询问是否可以放弃对SQL 2000的支持。如果答案是肯定的，那么您可以编写一个CLR过程来使用所有12个resultset，只取您想要的一个，并对其进行过滤。
- Give up and call the original procedure from your client code, but find a way of measuring the performance drop, so that you can exert some influence on the decision-making backed up with hard data.
- 放弃并从客户端代码中调用原始过程，但是找到一种度量性能下降的方法，这样您就可以对硬数据支持的决策施加一些影响。
#2
1
No, you can't. A stored procedure is a single executable entity.
不,你不能。存储过程是一个可执行实体。
You have to create a new stored proc (to return what you want) or modify the current one (to branch) if you want to do this: project politics can not change real life
您必须创建一个新的存储proc(以返回您想要的)或修改当前的proc(到branch)(如果您想这样做的话):项目政治不能改变现实生活
Edit: I didn't tell you this...
编辑:我没有告诉你这个……
For every bit of data you need from the database, call the stored procedure each time and use the bit you want.
对于数据库中需要的每个数据位，每次都调用存储过程并使用所需的位。
Don't "re-use" a call to get more data and cache it. After all, this is surely the intention of your Frankenstein stored procedure to give a consistent contract between client and databases...?
不要“重用”调用来获取更多数据并缓存它。毕竟，这肯定是您的弗兰肯斯坦存储过程的意图，即在客户端和数据库之间提供一致的契约……?
#3
0
You can try to make SQL CLR stored procedure for handle all tables returned by your stored procdure and in C# code to find data you need and return what you need. But I think that is just is going to make things more complicated.
您可以尝试使用SQL CLR存储过程来处理存储procdure返回的所有表，并使用c#代码查找所需的数据并返回所需的数据。但我认为这会让事情变得更复杂。
When you fill your dataset with sored procedure which return more results sets in data set you get for each result set one DataTable.
当您用排序过程填充数据集时，它将返回数据集中的更多结果集，您将为每个结果集获得一个DataTable。