An INSERT EXEC statement cannot be nested.

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

An INSERT EXEC statement cannot be nested.

Author

Topic

paulnamroud
Starting Member

26 Posts

Posted - 2010-03-31 : 19:54:43

Hello,

I'm getting the following error message: An INSERT EXEC statement cannot be nested.

I have 3 differents procedure
- pr_get_total_ar:
It returns Amount $ and Currency Id

- pr_check_credit
It returns only one signle value: True/False

- pr_add_sales_order

The procedure pr_check_credit do some validation and call the pr_get_total_ar to get amount$ and currency id:
Here's how i call it:
Insert into @tmp_table
Exec dbo.pr_get_total_ar param1, param2

Till now evrything works fine.

The last procedure pr_add_sales_order do some validation call the procedure "pr_check_credit"

Here's how i call it:
Insert into @tmp_table1
Exec dbo.pr_check_credit param1, param2

So here's my problem:
---------------------
If try to run the first 2 procedures separately everything works fine. But, when i try to run the procedure: pr_add_sales_order it generates the following error:

Msg 8164, Level 16, State 1, Procedure pr_check_credit, Line 112
An INSERT EXEC statement cannot be nested.
Warning: Null value is eliminated by an aggregate or other SET operation.

If i open the procedure pr_check_credit at the line 112 I find thie following code:
Insert into @tmp_table
Exec dbo.pr_get_total_ar param1, param2

Can anyone help me ? Or give me a workaround to fix this problem ?

Thank you

Paul

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-01 : 05:06:47

why dont you make inner procedure as a udf if you can?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

paulnamroud
Starting Member

26 Posts

Posted - 2010-04-01 : 09:12:03

Can you elaborate more about UDF and inner procedure ?
Thank you ...

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-01 : 12:04:25

http://www.sqlteam.com/article/user-defined-functions

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources