Technical: Microsoft – SQL Server – Error – “An INSERT EXEC statement cannot be nested.”

Technical: Microsoft – SQL Server – Error – “An INSERT EXEC statement cannot be nested.”

Introduction

I am trying to write a couple of generic Stored Procedures.

And, finding that the sp_executesql API offers a very graceful and compelling ability to express SQL in nice pre-defined template language.

All is well, if the resultant dataset is consumed within the same Stored Procedure.

But, errors occur when you try to expose the resultant dataset and consume it in a higher-up Stored Procedure.

Background

This is a bit out there! It will only affect a bit of people. I first experienced it a bit of years ago and I read through Erland Sommarskog excellent blog posting:

How to Share Data between Stored Procedures
http://www.sommarskog.se/share_data.html

And, I will suggest that you read his very comprehensive public labor.

Code (Problematic)

There are two Stored Procedures:

  • dbo.usp_getData1 (Inner SP – Uses sp_executesql, returns resultset)
  • dbo.usp_getData1_Outer (Outer SP … calls usp_getData1)

Code



use [tempdb]
go

if OBJECT_ID('dbo.usp_getData1') is null
begin
    exec ('create procedure dbo.usp_getData1 as select 1/0 as [undefined]')
end
go

alter procedure dbo.usp_getData1
as

	set nocount on;

	declare @collector TABLE
	(
		[cnt] int
	)

	insert into @collector
	(
		[cnt]
	)
	EXECUTE sp_executesql N' declare @cnt int; select @cnt as cnt'
go

if OBJECT_ID('dbo.usp_getData1_Outer') is null
begin
	exec ('create procedure dbo.usp_getData1_Outer as select 1/0 as [undefined]')
end
go

alter procedure dbo.usp_getData1_Outer
as

	set nocount on;
	declare @collector TABLE
	(
		[cnt] int
	)
	insert into @collector
	(
		[cnt]
	)
	exec dbo.usp_getData1

	select *
	from   @collector
go

SQL:
We ran the SQL below and tried invoking dbo.usp_getData1_Outer

exec dbo.usp_getData1_Outer

Error:

But, received the error pasted below:


Msg 8164, Level 16, State 1, Procedure usp_getData1, Line 13
An INSERT EXEC statement cannot be nested.

Code (Fix)

In our case, we only needed to return a Scaler value and so we changed our inner Stored Procedure a bit.

There are two Stored Procedures:

  • dbo.usp_getData2 (Inner SP – Uses sp_executesql, but does not return resultset, but instead an output variable)
  • dbo.usp_getData2_Outer (Outer SP … calls usp_getData1)


if OBJECT_ID('dbo.usp_getData2') is null
begin
    exec ('create procedure dbo.usp_getData2 as select 1/0 as [undefined]')
end
go

alter procedure dbo.usp_getData2
as

	declare @cnt int

	set @cnt = 12

	EXECUTE sp_executesql 
		    N'select @cntOut = 45'
		  , N'@cntOut int output'
		  , @cntOut = @cnt output

	select @cnt as [cnt]

go

if OBJECT_ID('dbo.usp_getData2_Outer') is null
begin
	exec ('create procedure dbo.usp_getData2_Outer as select 1/0 as [undefined]')
end
go

alter procedure dbo.usp_getData2_Outer
as

	set nocount on;
	declare @collector TABLE
	(
		[cnt] int
	)
	insert into @collector
	(
		[cnt]
	)
	exec dbo.usp_getData2

	select *
	from   @collector

go

SQL:
We ran the SQL below and tried invoking dbo.usp_getData2_Outer

exec dbo.usp_getData2_Outer

Now we are good!

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s