title: Temp Table Garbage Collection
body: <p>I seem to be running into a problem with temp table garbage collection:</p>
  
  <pre><code>CREATE PROCEDURE dbo.SetTestTempTable(@value bit)
  AS
  SET NOCOUNT ON
  IF OBJECT_ID('tempdb..#TestTempTable') IS NOT NULL 
      DROP TABLE #TestTempTable
  SELECT @value AS Value INTO #TestTempTable
  
  GO
  
  EXEC dbo.SetTestTempTable 1
  SELECT * FROM #TestTempTable
  </code></pre>
  
  <p>The above code produces the error </p>
  
  <pre><code>Msg 208, Level 16, State 0, Line 3
  Invalid object name '#TestTempTable'.
  </code></pre>
  
  <p>I think because #TestTempTable is getting garbage collected when the proc exits.</p>
  
  <p>Is there a way of preventing this? I don't want to have every caller need to explicitly create the temp table before calling the procedure.</p>
  
  <p>UPDATE:
  Why am I doing this?</p>
  
  <p>I have the need to store some contextual information (basically a session variable). I was using CONTEXT_INFO for this. SQL Azure doesn't support CONTEXT_INFO, so I'm refactoring accordingly. Essentially, I have a function:</p>
  
  <p>GetMySessionVariableName()</p>
  
  <p>and a procedure</p>
  
  <p>SetMySessionVariableName(value)</p>
  
  <p>Previously, this function and procedure used CONTEXT_INFO internally, and that worked fine. Now, with temp tables, it doesn't...I'm open to suggestions on alternative approaches. </p>