Why can't use INSERT EXEC statement within a stored procedure called by another stored procedure?

Question

Hi,

First I try to explain the circumstances. I store the the filter expression in one column separated by line breaks. The base idea was this:

SELECT  
'SELECT ''' + REPLACE(topic_filter,CHAR(10),''' UNION ALL SELECT ''') + ''''
FROM dbo.topic_filter T
WHERE
  T.id = @id
FOR XML PATH('')

After this I simply execute this string to put the datas into a temp table. My problem starts here. The snippet is in a stored procedure and used by multiple stored procedures to generate the base source to fill.
Approach 1:
Call this sp from another SP to fill a temp table.
Result 1:
An INSERT EXEC statement cannot be nested. (If I call simply with exec dbo... style the code is working. I only get the error if I try to call within a stored procedure)

Approach 2:
I put the code above into a table values function.
Result 2:
Invalid use of a side-effecting operator 'INSERT EXEC' within a function. (The function itself don't compiled)

Thanks,
Péter

Answer 1

It is an SQL Server restriction. You cannot have a nested insert exec (I'm not sure why).

If you go:

insert into t(value)
exec dbo.proc

, and inside dbo.proc you have

insert into t2(value2)
exec(@str)

, then it will not run.

Consider different ways of passing tables around, such as temporary tables or table-valued parameters.

Answer 2

Hi,

In the meantime I managed to solve the problem (with help :) ). The solution is simple:

exec('insert into t2 ' + @str)

Where @str contains a select statement.
I don't know why but this way there is no error. The method I call the stored procedure:

SET @exec = 'exec dbo.trFilterTopic ''' + @id+ ''',null,null,1'
INSERT INTO #filtered
exec (@exec)

I hope I spare some time to other folks with this solution.
Bye,
Péter