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

First I try to explain the circum开发者_如何学Pythonstances. 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

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

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.

Functions on SQL Server have limitations, they arenot procedures, you can't use dynamic SQL like 'EXECUTE STRING', 'INSERT EXEC'...