472,119 Members | 1,480 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,119 software developers and data experts.

About: An INSERT EXEC statement cannot be nested.

Hi all,
When I use following sql to get data in stored procedure, error occurs:
insert into #tmp
EXECUTE dbo.prc_1 @date1,@date1

Here is the source code, Thanks
[aa] [int] NULL ,
[cc] [int] NULL

insert into pp values(1,2)
insert into pp values(11,22)
create PROCEDURE prc_2
(@date1 smalldatetime,
@date2 smalldatetime)
create table #tmp (aa int, bb int)
insert into #tmp
EXECUTE dbo.prc_1 @date1,@date1
select * from #tmp
drop table #tmp

create PROCEDURE prc_1
(@date1 smalldatetime,
@date2 smalldatetime)
declare @sql Nvarchar(2000)
create table #t2 (aa int, bb int)
set @sql = ''
set @sql = 'insert into #t2 select aa,cc from pp'
exec sp_executesql @sql
select * from #t2
drop table #t2
Here is the error message:
DECLARE @date1 datetime
DECLARE @date2 datetime
SELECT @date1 = '20050101'
SELECT @date2 = '20050303'
EXEC @RC = [ExportServerDB].[dbo].[prc_2] @date1, @date2
DECLARE @PrnLine nvarchar(4000)
PRINT 'Stored Procedure: ExportServerDB.dbo.prc_2'
SELECT @PrnLine = ' Return Code = ' + CONVERT(nvarchar, @RC)
PRINT @PrnLine

Server: Msg 8164, Level 16, State 1, Procedure prc_1, Line 15
An INSERT EXEC statement cannot be nested.
(0 row(s) affected)
(0 row(s) affected)

Stored Procedure: ExportServerDB.dbo.prc_2
Return Code = 0

Does anyone have some comments about it? Thans
Nov 28 '05 #1
0 3547

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

7 posts views Thread by Bill Kellaway | last post: by
5 posts views Thread by TPJ | last post: by
reply views Thread by leo001 | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.