title: Why can't use INSERT EXEC statement within a stored procedure called by another stored procedure?
body: <p>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:  </p>
  
  <pre><code>SELECT  
  'SELECT ''' + REPLACE(topic_filter,CHAR(10),''' UNION ALL SELECT ''') + ''''
  FROM dbo.topic_filter T
  WHERE
    T.id = @id
  FOR XML PATH('')
  </code></pre>
  
  <p>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.<br>
  Approach 1:<br>
  Call this sp from another SP to fill a temp table.<br>
  Result 1:<br>
  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)</p>
  
  <p>Approach 2:<br>
  I put the code above into a table values function.<br>
  Result 2:<br>
  Invalid use of a side-effecting operator 'INSERT EXEC' within a function.
  (The function itself don't compiled)</p>
  
  <p>Thanks,<br>
  Péter</p>