Returning a subset of a stored procedure

Question

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.

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?

I have to support sql server 2000 +

Answer 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

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

Answer 2

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.

When you fill your dataset with sored procedure which return more results sets in data set you get for each result set one DataTable.

Answer 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:

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