title: TSQL: running exec after INSERT
body: <p>I need to convert old data from multiple tables. I only just found out now, after writing the stored procedure to do this for one table, that it needs to also handle data from a couple other tables. This means that my procedure needs to be changed to properly handle this. I would like to do this dynamically, while keeping away from performance hits. I learned about using exec sp_executesql @SQLStatement to avoid performance issues with dynamic SQL.</p>
  
  <p>However, if I write the below, I get no results:</p>
  
  <pre><code>SET @SQLStatement = 'select CONVERT(XML, ' + @ConvColumn + ') ' + @AdditionalColumns + ' from ' + @ConvTable
  INSERT INTO @TempDataConv
  exec sp_executesql @SQLStatement
  </code></pre>
  
  <p>Yet, if I write it explicitly defining the table and columns, this works just fine:</p>
  
  <pre><code>INSERT INTO @TempDataConv
  SELECT
      DecPageID,
      PolicyID,
      PolicyNumber,
      CONVERT(XML, DecInfo),
      null
  FROM [InternalPortal].[dbo].Decpages
  </code></pre>
  
  <p>What might be going on? As no results are returned, I am unsure how to figure out the problem. I assume that perhaps I cannot do the below. Is that true? Should I simply write this out in if blocks for the different tables?</p>
  
  <pre><code> INSERT INTO @TempDataConv
  exec sp_executesql @SQLStatement
  </code></pre>