By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,841 Members | 840 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,841 IT Pros & Developers. It's quick & easy.

About: An INSERT EXEC statement cannot be nested.

P: n/a
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
CREATE TABLE [pp] (
[aa] [int] NULL ,
[cc] [int] NULL
) ON [PRIMARY]
GO

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

--proc1
create PROCEDURE prc_1
(@date1 smalldatetime,
@date2 smalldatetime)
AS
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 @RC int
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
Share this question for a faster answer!
Share on Google+

This discussion thread is closed

Replies have been disabled for this discussion.