Prev: Sub Queries and Calculation
Next: Where clause not working
From: tshad on 23 Mar 2010 14:10
I just got a message trying to use Insert/Exec that said:

An INSERT EXEC statement cannot be nested.

Took me awhile to get what that meant.

What it really means is that an INSERT EXEC cannot be in a nested Stored
Procedure.

So I can call a SP directly that has an INSERT EXEC but I can't call that
same procedure from another SP.

So it is the SP that is nested and not the INSERT EXEC.

Tom


From: Erland Sommarskog on 23 Mar 2010 18:57
tshad (tfs(a)dslextreme.com) writes:
> I just got a message trying to use Insert/Exec that said:
>
> An INSERT EXEC statement cannot be nested.
>
> Took me awhile to get what that meant.
>
> What it really means is that an INSERT EXEC cannot be in a nested Stored
> Procedure.
>
> So I can call a SP directly that has an INSERT EXEC but I can't call that
> same procedure from another SP.
>
> So it is the SP that is nested and not the INSERT EXEC.

Not really sure what you are trying to say here, but the error means
exactly what it says. If you say:

INSERT tbl EXEC some_sp

And some_sp in its turn uses INSERT EXEC, or calls another procedure that
does, then you get this error.

INSERT-EXEC is useful sometimes, but there are usually better solutions.
I have an article on my web site that discusses different alternatives:
http://www.sommarskog.se/share_data.html


--
Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

 | 
Pages: 1
Prev: Sub Queries and Calculation
Next: Where clause not working