An INSERT EXEC statement cannot be nested.

I try to select a store procedure in SqlExpress2005 which inside store procedure execute another store procedure,
When I select it but it prompt error messages "An INSERT EXEC statement cannot be nested.".
In Fire bird /Interbase store procedure we can nested. Below are the code;

declare @.dtReturnData Table(doccode nvarchar(20), docdate datetime, debtoraccount nvarchar(20))
Insert Into @.dtReturnData
Exec GetPickingList 'DO', 0, 37256, 'N', 'N', 'YES'

Select doccode, docdate, debtoraccount
From @.dtReturnData

Inside the GetPickList It will do like this, but most of the code I not included;

ALTER PROCEDURE GETPICKINGLIST
@.doctype nvarchar(2),
@.datefrom datetime,
@.dateto datetime,
@.includegrn char(1),
@.includesa char(1),
@.includedata nvarchar(5)
AS
BEGIN
declare @.dtReturnData Table(doccode nvarchar(20),
docdate datetime,
debtoraccount nvarchar(20))

IF (@.DOCTYPE = 'SI')
BEGIN
Insert Into @.dtSALESINVOICEREGISTER
Exec SALESINVOICEREGISTER @.DateFrom, @.DateTo, @.IncludeGRN, @.IncludeSA, @.IncludeData
END
ELSE
BEGIN
Insert Into @.dtDELIVERYORDERREGISTER
Exec DELIVERYORDERREGISTER @.DateFrom, @.DateTo, @.IncludeGRN, @.IncludeSA, @.IncludeData
END
Select doccode,docdate,debtoraccount From @.dtReturnData

END

So how can I select a nested store procedure? can someone help me

Jeremy,

This is a problem that comes up from time to time in SQL Server. My first suggestion when this comes up is to look at both points in which you are using the INSERT ... EXEC syntax. See if it is possible to convert at least one of the procedures into a user defined function. There is a "Plan C" for this but it is not nearly as clean as the option of converting to a function (if possible).

Also, for future keep in mind that it is a good idea to consider using functions -- especially inline functions -- instead of stored procedure when it is the intent to load the output from a procedure into a table.

See if SalesInvoiceRegister and DeliveryOrderRegister can be converted to functions.

Kent

An INSERT EXEC statement cannot be nested.

i wanted to store the output of my store proc in a temp table and i wsa doin
d
this:
INSERT #temp EXEC sproc
and it turned out i cannot do this if my sproc has another insert...exec
thing going on within it.
Is there any way i can store the output of my sproc somehow ?
thanks in advanceAbhishek Pandey (AbhishekPandey@.discussions.microsoft.com) writes:
> i wanted to store the output of my store proc in a temp table and i wsa
> doind this: >
> INSERT #temp EXEC sproc
> and it turned out i cannot do this if my sproc has another insert...exec
> thing going on within it.
> Is there any way i can store the output of my sproc somehow ?
Answered in comp.databases.ms-sqlserver. Please to do not post to multiple
newsgroups independently.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

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

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.googlegroups.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.googlegroups.com...

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.googlegroups.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:
> 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.googlegroups.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.
> >