title: The scope and lifespan of temp table created in nested sp calls in TSQL
body: <p>I revised some of the sp with temp table to eliminate the dozends of <code>declare  @vars</code> in each sps. Runing individually, they seem to work great, until I wrote a test case script, looping over the live records in production system to test these unreleased new sps (ATM, I can't think of better ways to loop through parameter sets to test them without a cursor. Please try not to focus on use of cursor here). I got different result during repeated <code>exec</code>.
  e.g. </p>
  
  <pre><code>-- sub_sp2: 
  -- sub_task2
  select top 1 col1, col2, .... col30 
         into #temp_sub_sp2 
  from tb1
  left join tbl_ext1 
  left join tbl_ext2
  left join tbl_ext3
  left join tbl_ext....
  where cond1 = true
  
  if @@row_count &lt; 0 
      print cond1=true do A thing
  else 
      print cond1=false do B thing
  
  -- sp1: 
  -- this is a master sp with a couple of sub tasks written in sub_sps.
  
  exec sub_sp2 param1,...., @task2_result output
  exec sub_sp3 param1,...., @task3_result output
  exec sub_sp4 param1,...., @task4_result output
  -- do sth with @result1
  
  -- test script:
  -- a test script to call sp1 repeatedly with 
  -- different parameters from production system to check result.
  declare cur1 ...
     for select colx from prd_mach_status
  open cur1
  fetch next from cur1 into @param1
  ...
  while ...
      exec sp1 @param1
  </code></pre>
  
  <p>The result is like </p>
  
  <pre><code>| param1 | print          |
  | value1   | cond1 = 1 do A |
  | value1   | cond1 = 0 do B |
  | value2   | cond1 = 0 do B |
  | value1   | cond1 = 0 do B |
  </code></pre>
  
  <p>The live data varies, but always follow the same pattern. After the 1st cond1 is true, the following exec will all result in cond1 = 0. Even if cond1 is true.</p>
  
  <p>Clearly the <code>select into</code> failed, after the first successful <code>select into</code>. and the temp table is not freed after SP2, or SP1 has terminated.</p>
  
  <p>Here the local <code>#temp_sp2</code> is created inside sp2's scope, not from outer scope, once the call returns, the creation scope is no longer valid, but the table is not freed. It feels like the latter call to the same sp shares the same scope with the previous calls.</p>
  
  <p>I already know the following :  </p>
  
  <ul>
  <li>Temp tables are just aliased tables with fancy names in tempdb. </li>
  <li>The concept of scopes defines their visibility</li>
  <li>Sessions are top level scopes seperated per connection.</li>
  </ul>
  
  <p>My question is about their lifespan. Usually once an object is created its bound to a local function scope, once the scope is gone, the object is GCed. The local temp table created in a nested sp here clearly didn't follow this. </p>
  
  <p>What is the catch here, how should it be used, when the regular understanding of scope doesn't apply here (e.g. python).</p>
  
  <p>The answer from <code>Scope of temporary tables in SQL Server</code> actually contradicts with this.</p>
  
  <blockquote>
    <p>Also, there's no need to DROP TABLE at the end of your procedure (from
    same link again):</p>
    
    <blockquote>
      <p>A local temporary table created in a stored procedure is dropped automatically when the stored procedure is finished</p>
    </blockquote>
  </blockquote>
  
  <p>From @Panagiotis-Kanavos 's comment, I seem to get the idea that temp table (to be exact their fancy alias mapping, not necessarily the real table) has a scope for visibility/creation but is not auto destroied until the session ends. This explains things, but controdicts with previous statements from suggested question's answer.</p>
  
  <blockquote>
    <p>Scope and Lifetime don't really apply. SQL isn't C#. #somename is a real table that's created when a create table statement is executed and exists as long as the session/connection is open. </p>
  </blockquote>
  
  <p>Now I am more confused...</p>