title: Scope of temporary tables in SQL Server
body: <p>I wrote a stored procedure to import and transform data from one database to another. Each import would take a single company ID and import all data related to this company.</p>
  
  <p>To help with the transformation step I use temporary tables. As part of script review, I was told to use table variables rather than temporary tables.
  The reviewer claims that if we run two different imports at the same time, the temporary table would be shared and corrupt the import.</p>
  
  <hr>
  
  <p><strong>Questions:</strong></p>
  
  <ul>
  <li>Is it true that the temporary table would be shared if we run two different imports at the same time?</li>
  <li>Does each call to <code>EXEC</code> create a new scope?</li>
  </ul>
  
  <hr>
  
  <p>Here is a contrived example of the script.</p>
  
  <pre class="lang-sql prettyprint-override"><code>CREATE PROC [dbo].[ImportCompany]
  (
      @CompanyId AS INTEGER
  )
  AS
  EXEC [dbo].[ImportAddress] @CompanyId = @CompanyId 
  --Import other data
  
  CREATE PROC [dbo].[ImportAddress]
  (
      @CompanyId AS INTEGER
  )
  AS
      CREATE TABLE #Companies (OldAddress NVARCHAR(128), NewAddress NVARCHAR(128))
      INSERT INTO #Companies(OldAddress, NewAddress)
      SELECT
          Address as OldAddress,
          'Transformed ' + Address as NewAddress
      FROM
          [OldDb].[dbo].[Addresses]
      WHERE
          CompanyId = @CompanyId
  
      --Do stuff with the transformed data
  
      DROP TABLE #Companies
  
  EXEC [dbo].[ImportCompany] @CompanyId = 12345
  </code></pre>