Alternative to Temporary table to store stored procedure results

I have come across the error "INSERT EXEC statement cannot be nested"
when trying to store the results of a stored procedure in a temporary
table. I understand why this is happening - because there is already
an INSERT EXEC in the stored procedure I am executing - but I need to
be able to store the results in some way.
Unfortunately re-writing the stored procedure I am calling is not an
option, and I was wondering if there is another way for me to evaluate
the results from my stored procedure.
I have looked into table variables and functions, but they do not work
here either.
I would appreciate anyone's input on this.
Are you refering to something like this:
USE PUBS
GO
CREATE PROC USP_TEMPPROC
AS
CREATE TABLE #TEST1 (AU_ID VARCHAR(25))
CREATE TABLE #TEST2 (AU_ID VARCHAR(25))
INSERT #TEST1
EXEC BYROYALTY 100
SELECT * FROM #TEST1
INSERT #TEST2
EXEC BYROYALTY 100
SELECT * FROM #TEST2
DROP TABLE #TEST1
DROP TABLE #TEST2
EXEC USP_TEMPPROC
--OR SOMETHING LIKE THIS:
USE PUBS
GO
CREATE PROC USP_TEMPPROC_2A
AS
CREATE TABLE #TEST1 (AU_ID VARCHAR(25))
INSERT #TEST1
EXEC BYROYALTY 100
SELECT * FROM #TEST1
EXEC USP_TEMPPROC_2B
DROP TABLE #TEST1
CREATE PROC USP_TEMPPROC_2B
AS
CREATE TABLE #TEST2 (AU_ID VARCHAR(25))
INSERT #TEST2
EXEC BYROYALTY 100
SELECT * FROM #TEST2
DROP TABLE #TEST2
EXEC USP_TEMPPROC_2A
Both seem to work.
HTH
Jerry
<c.williamson@.dialaphone.com> wrote in message
news:1128098603.949372.70830@.z14g2000cwz.googlegro ups.com...
>I have come across the error "INSERT EXEC statement cannot be nested"
> when trying to store the results of a stored procedure in a temporary
> table. I understand why this is happening - because there is already
> an INSERT EXEC in the stored procedure I am executing - but I need to
> be able to store the results in some way.
> Unfortunately re-writing the stored procedure I am calling is not an
> option, and I was wondering if there is another way for me to evaluate
> the results from my stored procedure.
> I have looked into table variables and functions, but they do not work
> here either.
> I would appreciate anyone's input on this.
>
|||More like the second example, but slightly different:
USE PUBS
GO
CREATE PROC USP_TEMPPROC_2A
AS
CREATE TABLE #TEST1 (AU_ID VARCHAR(25))
INSERT #TEST1
EXEC USP_TEMPPROC_2B
DROP TABLE #TEST1
CREATE PROC USP_TEMPPROC_2B
AS
CREATE TABLE #TEST2 (AU_ID VARCHAR(25))
INSERT #TEST2
EXEC BYROYALTY 100
SELECT * FROM #TEST2
DROP TABLE #TEST2
EXEC USP_TEMPPROC_2A
When running the last line I get "An INSERT EXEC statement cannot be
nested", because in both stored procedures I am trying to store results
from the sp in a temporary table.
I cannot re-write the second stored procedure, so I need some way to
work with the results in the first stored procedure.
Thank you
Christian
Jerry Spivey wrote:[vbcol=seagreen]
> Are you refering to something like this:
> USE PUBS
> GO
> CREATE PROC USP_TEMPPROC
> AS
> CREATE TABLE #TEST1 (AU_ID VARCHAR(25))
> CREATE TABLE #TEST2 (AU_ID VARCHAR(25))
> INSERT #TEST1
> EXEC BYROYALTY 100
> SELECT * FROM #TEST1
> INSERT #TEST2
> EXEC BYROYALTY 100
> SELECT * FROM #TEST2
> DROP TABLE #TEST1
> DROP TABLE #TEST2
> EXEC USP_TEMPPROC
> --OR SOMETHING LIKE THIS:
> USE PUBS
> GO
> CREATE PROC USP_TEMPPROC_2A
> AS
> CREATE TABLE #TEST1 (AU_ID VARCHAR(25))
> INSERT #TEST1
> EXEC BYROYALTY 100
> SELECT * FROM #TEST1
> EXEC USP_TEMPPROC_2B
> DROP TABLE #TEST1
> CREATE PROC USP_TEMPPROC_2B
> AS
> CREATE TABLE #TEST2 (AU_ID VARCHAR(25))
> INSERT #TEST2
> EXEC BYROYALTY 100
> SELECT * FROM #TEST2
> DROP TABLE #TEST2
> EXEC USP_TEMPPROC_2A
> Both seem to work.
> HTH
> Jerry
> <c.williamson@.dialaphone.com> wrote in message
> news:1128098603.949372.70830@.z14g2000cwz.googlegro ups.com...