Scope of temporary tables in SQL Server -

i wrote stored procedure import , transform data 1 database another. each import take single company id , import data related company.

to transformation step use temporary tables. part of script review, told use table variables rather temporary tables. reviewer claims if run 2 different imports @ same time, temporary table shared , corrupt import.

---

questions:

• is true temporary table shared if run 2 different imports @ same time?
• does each call exec create new scope?

---

here contrived example of script.

create proc [dbo].[importcompany] (     @companyid integer ) exec [dbo].[importaddress] @companyid = @companyid  --import other data  create proc [dbo].[importaddress] (     @companyid integer )     create table #companies (oldaddress nvarchar(128), newaddress nvarchar(128))     insert #companies(oldaddress, newaddress)     select         address oldaddress,         'transformed ' + address newaddress             [olddb].[dbo].[addresses]             companyid = @companyid      --do stuff transformed data      drop table #companies  exec [dbo].[importcompany] @companyid = 12345 

from create table:

local temporary tables visible in current session

and (more importantly):

if local temporary table created in stored procedure or application can executed @ same time several users, database engine must able distinguish tables created different users. database engine internally appending numeric suffix each local temporary table name.

which rebuts point of whoever said shared.

---

also, there's no need drop table @ end of procedure (from same link again):

a local temporary table created in stored procedure dropped automatically when stored procedure finished