February 22, 2012 at 3:35 am
Dear all;
pls any one help
Create proc get_result(@id int)
as
create table #tmp(cidint)
insert into #tmp(cid)
exec proctest @id,2
select * from #tmp
This is my procedure
In this procedure is calling the another procedure take the parameter of the parent procedure and pass the childproceduer to insert the temporary table
in this case to run the exeucte the sp it throw the error
The criteria we don't used in ad hoc queries sove the procedure method only
any one help this problem.
Msg 8164, Level 16, State 1, Procedure search, Line 380
An INSERT EXEC statement cannot be nested.
February 22, 2012 at 3:47 am
February 22, 2012 at 4:00 am
Nested INSERT...EXEC statements are not supported (this is a feature that will not be supported as per connect -> https://connect.microsoft.com/SQLServer/feedback/details/272133/cannot-have-nested-insert-exec )
Check http://www.sommarskog.se/share_data.html#INSERTEXEC for a more detailed description on the issues of using INSERT...EXEC and other alternatives that are available to share data between procedures.
February 22, 2012 at 7:56 am
One possible solution is to have ChildProcedure check for the existence of table #tmp and insert its values directly into the table rather than returning them as a result set.
IFOBJECT_ID('TempDB.dbo.#tbl') IS NOT NULL
-- The table exists; put the rows into the table.
INSERT INTO #tbl ...
SELECT ...
ELSE
-- The table does not exist; return rows as a result set.
SELECT ...
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply