title: Calling Stored procedure with cross apply from another stored procedure gives error SQL server
body: <p>I have been trying to call stored procedure from another stored procedure. Now issue is that under lying nested stored procedure contains CROSS APPLY with temp table and it runs fine when i execute it directly.</p>
  
  <p>But when i try to call this SP from other SP, it gives error that one of the column is invalid. "Invalid column name 'levels'" in this case.</p>
  
  <p>Plus, when i execute this SP from calling SP SQL window with passing parameters, it runs fine and whole main procedure starts running smoothly.</p>
  
  <p>I am not able to get why this issue happens. Below is kind of implementation for reference.
  1.) Main SP</p>
  
  <pre><code>CREATE STORED PROCEDURE ....
  
  INSERT INTO #TempTable
  EXEC [Child_SP] @Param1 = 1, @Param2 = 1
  ...
  </code></pre>
  
  <p>Gives error.
  2.) Once i execute below given as single statement once from main PS. It starts working fine.</p>
  
  <pre><code>EXEC [Child_SP] @Param1 = 1, @Param2 = 1
  </code></pre>
  
  <p>3.) Child SP has CROSS APLLY with one of the temp table. something like below.</p>
  
  <pre><code>SELECT ID, '1,2,3,4,5' AS levels
  INTO #Temp1
  FROM ABC
  
  SELECT ID
  FROM #Temp1 x0
  CROSS APPLY (SELECT * FROM dbo.iter_charlist_to_table(x0.levels, ',') AS x) x1 
  WHERE x1.listPos &gt; 1
  </code></pre>
  
  <p>"iter_charlist_to_table" is table value function which get values as table from comma seperated list.</p>
  
  <p>Is it related to SQL Thread anyhow or whats the issue? Thanks.</p>