title: SQL Server Stored Procedure with multiple batches - reuse of temp table
body: <p>I have a short SQL script which has been in use for a bit. It reuses a temp table within a script and has been working nicely.   </p>
  
  <p>Recently, I decided that I'm going to stick this whole thing into a procedure, though I had a surprise waiting for me - there's a use of the keyword <code>GO</code> in order to divide the script into two batches (that is how I was able to reuse the temp table) - which is why SQL Server is barking at me. </p>
  
  <p>Here is a dumbed down script displaying the functionality of the script:</p>
  
  <pre><code>DROP TABLE IF EXISTS #temp;
  SELECT
      'john' AS first_name
      ,'doe' AS last_name
  INTO #temp;
  
  SELECT * FROM #temp
  GO 
  TRUNCATE TABLE #temp;
  
  DROP TABLE #temp;
  
  SELECT
      'jane' AS first_name
      ,'doe' AS last_name
  INTO #temp;
  </code></pre>
  
  <p>Here's what I tried to do in the procedure, albeit unsuccessfully:</p>
  
  <pre><code>CREATE PROCEDURE #temp_proc 
  AS
  BEGIN
      DROP TABLE IF EXISTS #temp;
      SELECT
          'john' AS first_name
          ,'doe' AS last_name
      INTO #temp;
  
      SELECT * FROM #temp
      GO 
      TRUNCATE TABLE #temp;
  
      DROP TABLE #temp;   
  
      SELECT
          'jane' AS first_name
          ,'doe' AS last_name
      INTO #temp;
  END
  </code></pre>
  
  <p>Here's the error message I get when I attempt to create the procedure:</p>
  
  <blockquote>
    <p>Msg 102, Level 15, State 1, Procedure #temp_proc, Line 10 [Batch Start Line 0]<br>
    Incorrect syntax near '#temp'.  </p>
    
    <p>Msg 102, Level 15, State 1, Line 20<br>
    Incorrect syntax near 'END'.</p>
  </blockquote>
  
  <p><strong>Objective</strong>: I'd like to continue reusing the same temp table names, though I would like to stick all of this in a procedure. Any ideas?</p>