Posted by Erland Sommarskog on 07/24/06 21:47

(masri999@gmail.com) writes:
> I want to share my experiences about using insert into exec which may
> help others .
> Using SQL Server 2000, SP3 .
>
> Two Proceduers - Parent SP caliing a Child SP (nested ) . No Explicit
> transactions .
>
> I have defined a # table in Parent SP and calling a Child SP like this
> insert into #temp exec childsp ......
>
> Child SP has Select * from local # temp table ( local to child SP
> ) as the last statement .
>
> When number of records are less ( around 1000 - 5000) Parent SP
> executes but slow .
>
> When the Child SP returns higher number of rows ( 1,00,000 or more )
> the SP will be running for hours with out completion .
> Although executing the child SP , with exec ChildSP .... with same
> parameters it is completed in 2 mins for 3,00,000 rows .
>
> Resolution : - Define a temp table (say #tempChild ) in the Parent SP
> .
> In the Child SP instead of select * replace with insert into
> #tempChild select * from ...

For further discussion on the problem of passing data between stored
procedures, there is an article on my web site:
http://www.sommarskog.se/share_data.html.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]