Select DATA From EXEC result

[Yuriy Galanter]

Hi all,

I have a stored procedure that return a result set. I need to be able to run
a selection on that result set, something like

SELECT * FROM EXEC...

I know it's a well known problem, but due to my situation I cannot use
commonly available solutions. The Stored procedure already uses INSERT
INTO... EXEC of it's own, so I cannot use INSERT INTO temp table outside of
it. I cannot modify the SP (it's used in many other places) so solutions
like passing parameters or create temporary table for SP outside of it are
out of the questions. I am also very reluctant to use OPENQUERY and
OPENROWSET methods.

Any alternatives?

Thanks in advance!

Yuriy.

[Andrew J. Kelly]

Why don't you create a new sp for this purpose and tune it as needed?
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors

Post by Yuriy Galanter
Hi all,
I have a stored procedure that return a result set. I need to be able to
run a selection on that result set, something like
SELECT * FROM EXEC...
I know it's a well known problem, but due to my situation I cannot use
commonly available solutions. The Stored procedure already uses INSERT
INTO... EXEC of it's own, so I cannot use INSERT INTO temp table outside
of it. I cannot modify the SP (it's used in many other places) so
solutions like passing parameters or create temporary table for SP outside
of it are out of the questions. I am also very reluctant to use OPENQUERY
and OPENROWSET methods.
Any alternatives?
Thanks in advance!
Yuriy.

[Erland Sommarskog]

Post by Yuriy Galanter
I have a stored procedure that return a result set. I need to be able to
run a selection on that result set, something like
SELECT * FROM EXEC...
I know it's a well known problem, but due to my situation I cannot use
commonly available solutions. The Stored procedure already uses INSERT
INTO... EXEC of it's own, so I cannot use INSERT INTO temp table outside
of it. I cannot modify the SP (it's used in many other places)

As long as you have access to the code, you can change it. Change it to:

CREATE some_sp AS
CREATE TABLE #temp (...)
EXEC inner_sp
SELECT * FROM #temp

innser_sp would hold the logic of some_sp, but instead of returning
a result set, it would write to the temp table.

Existing calls to the procedure would not be affected. In the code you
are working with now, you would call inner_sp.