select from stored procedure ?

[prasadmokashi]

Hi,

I have a stored procedure which gives me some output.

Can I create select query on the output of this stored procedure ?

Thanks,
Prasad

 

[DaveMere]

I don't know about that, but have you considered using nested queries instead?

 

[nicatt]

I am curious as to what you would like to accomplish. The stored proc outputs data that you use for something and you would like to use that same output for additional purposes not related to the first output? Your first proc outputs data but you don't use it for anything and you want to write another proc that will use the data? Please clarify what you are trying to do.

 

[prasadmokashi]

Hi,

Stored procedure is having complex logic which fetches data from different tables. I don't have any control over this stored procedure so I can not update it to suit my requirement.( It is owned by another group ). This output is very useful for me instead of reimplementing same logic. But at the same time I would like to put some filters on that output to make it more suitable for my requirement.

Thanks,
Prasad

 

[janousek4]

Hi,
I a have similar problem where I need to query a stored proc. In my environment changing Sp or creating a new one is no option.
(The SP returns ~52 cols where I need only 11 cols. Getting all data over 256k link is causing all sort of problems to me.)
any ideas?

 

[ChrisCarroll]

Showing off my ignorance here, but in MS SQL Server the only way was, I think:

Insert into #temptable Exec storedprocedure.
Select <the columns you want> from #temptable

 

[nicatt]

Here is my suggestion. Since you want to use the output of the stored procedure you must be on the same platform. Your biggest problem is that the first proc's data is transient and will be deleted after the proc completes its run. To use its data you would have to catch it before the proc completes and the first proc would have to call your proc. I have seen this done but all the procs were owned by the same process and called one another.

I would recommend you ask the owner of the first stored proc to dump the results into a table you create just before it outputs the final data. You would then run a stored proc against the table extracting only that data you need. You can truncate the table after you are done with it or delete old data prior to using it based upon some parameter. The first proc could also truncate the table.

 

[srb1]

Considering that your proc returns only one row with several columns (lets say 52 cols) you may create a wrapper proc defined with equal number of local variables to store these values and then have a select immediate after the proc call with selected columns (say 11 of 52).

Incase the original proc returns multiple rows, you either need to find the combination of input values to make it return only one row. If this can be achieved, the wrapper proc would execute the original proc in loop to make it return individual rows each time and hence the above solution would work.
If a combination of input values cannot be made so as to return a single row, the proc would have to be modified accordingly.

If all these fails, the best is to have the results saved in a temp table (could be a permanent temp table...you know what it is) and have control on it.

HTH