Posted by Scarab on 11/28/05 09:44

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

[Back to original message]