An INSERT EXEC statement cannot be nested

move the “insert into” from nest_1 inside of the dynamic sql – the idea is that you are hiding the insert into exec contained within the parent from the child.

USE test
GO

ALTER PROC nest_1
AS

CREATE TABLE #t (a INT)
INSERT INTO #t EXEC (‘ select 2 a’)

SELECT * FROM #t
GO

ALTER PROC nest_2
AS

CREATE TABLE #s (a INT)
INSERT INTO #s
EXEC (‘test..nest_1’)

SELECT * FROM #s
GO

exec nest_2

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

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


%d bloggers like this: