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

an insert

Hello,

I need to realize an insert something like the following:

Exec GetMyID @.tName, @.MyId OUTPUT

INSERT INTO MyTable2 (MyId,MyName)

SELECT @.MyId,MyName

FROM MyTable1

Here I am getting MyID from a stored procedure and I need to insert this to MyTable2, however I need to get a new MyID for each row in MyTable1. How can I do that?

I think you will have to do a loop here and do the EXEC for each loop.|||

You could try wrapping the stored procedure in a user defined function, then referencing it in your insert. So if the function was fn_NewKey, it'd be something like

Insert into MyTable2(MyID, MyName)

select fn_NewKey() as fred, MyName

from MyTable1

Incidentall, why don't you just use an identity column for MyID? It would be a lot easier.

|||I think you will have to do a loop here and do the EXEC for each loop.|||aaaaaaaaaaaa

an insert

Hello,

I need to realize an insert something like the following:

Exec GetMyID @.tName, @.MyId OUTPUT

INSERT INTOMyTable2 (MyId,MyName)

SELECT@.MyId,MyName

FROMMyTable1

Here I am getting MyID from a stored procedure and I need to insert this to MyTable2, however I need to get a new MyID for each row in MyTable1. How can I do that?

you could write a view which uses a cursor to step through all the names and inserts the names and the result of that proc into a table, and then insert that.

What does the proc do ? Where does the ID come from that you need to use the proc ?

|||Please don't go the route of using cursors or writing procedural code. SQL is a set-based language and you will get the best performance if you utilize it. What does the SP GetMyID do? Why can't you use IDENTITY column on the table to generate automatic sequential numbers? This is a much more efficient mechanism. You can get the multiple ids generated by SQL Server using OUTPUT clause in SQL Server 2005 or via a trigger that dumps the rows from inserted table in SQL Server 2000 or requery base-table using the alternate key values from the rows that you inserted.|||

I would concur that a cursor is a last resort, but I was assuming there was a reason that the proc needs to be called on a line by line basis ( hence my questions about the nature of hte proc also ).

I was also assuming he needs to do a single insert, not that this code is going to be run regularly. b/c if the insert needs to happen on an ongoing basis, it should happen as each record is created.

|||

GetMyID Return an Id which is unique. This is SQL 2000, can you give me an example of trigger. I am just trying to get a new id for each row in my table.

|||

It generates an ID ? Ideally, I would have an identity column in the first table, and just insert the ID from the first table into the second ( so the string is only in your database once, and you can change it there to see it change throughout the database ).

A trigger is a proc that is fired when you perform a specific action such as an insert into a specific table. The help has lots of info on how they work.

|||

Hi cgraus,

That is true it is an id, however it is not identity, the way that the system is designed it creates a specific id. Is there any trigger example you can give me to accomplish to call the stored procedure for each row inserted into MyTable2 meaning accomplishes the following for each row.

Exec GetMyID @.tName, @.MyId OUTPUT

INSERT INTO MyTable2 (MyId,MyName)

SELECT @.MyId,MyName

FROM MyTable1

|||

I guess you could do a trigger that runs when you insert a name in to MyTable2 ( I assume these names are contrived ), which calls the proc to generate the ID.

If an ID exists, why don't you store it in the table and use it throughout the system, regardless of where it comes from ?

http://www.codeproject.com/database/SquaredRomis.asp

Basically, the syntax is

CREATE TRIGGER invUpdate ON [Table2]
FOR INSERT

AS

...

Inside an insert trigger, the 'inserted' table will give you access to the items that were inserted. I am not sure if it's called once per line. If not, then a bulk insert leaves you with the same problem, but just within the trigger.

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