bugged.be

"Think outside the [B0X]"
Icon

An insert exec statement cannot be nested

Avoid ‘An insert exec statement cannot be nested’ by preparing a temp table from calling procedure and populate it by inner stored procedure.

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
--exec BDC_Inner
ALTER  procedure BDC_Inner
as
if not exists(select ID from tempdb.dbo.sysobjects where name like '#T%') create table #T (A varchar(3))
insert into #T values ('AAA')
--select * from #T
if @@nestlevel=1 drop table #T

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
--exec BDC_outer
ALTER  procedure BDC_outer
as
create table #T (A varchar(3))
exec BDC_Inner
select * from tempdb..sysobjects where name like '#%'
insert into #T values ('ZZZ')
select * from #T
drop table #T

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

Category: Sql

Tagged: