Using temp tables with the same name in a stored procedure

Question

If I have a very basic stored procedure like this in SQL Server 2012:

Create Procedure [MyStoredProcedure] (@ input as int)

As


Select 1 as col1, 2 as Col2
Into #mytemp1

Drop Table #mytemp1

Select 3 as col1, 4 as Col2, 5 as Col3
Into #mytemp1

Drop Table #mytemp1

and I try and run it it fails with the error 'There is already an object named '#mytemp1' in the database.' If this wasn't a stored procedure I could use GO after I initially drop the temp table. Is there a way around this? Thanks

Answer 1

Since there is no explicit need to re-use the same temp table name, just use unique names for each temp table.

Well, technically you could do something like the following:

EXEC('
  Select 1 as col1, 2 as Col2
  Into #mytemp1

  something else related to #mytemp1
');

EXEC('
  Select 3 as col1, 4 as Col2, 5 as Col3
  Into #mytemp1

  something else related to #mytemp1
');

That would not fail as each temp table is isolated in a subprocess that is not parsed until the EXEC actually runs. And, the temp table disappears when the EXEC is finished (hence no need for the explicit DROP statements). But in most cases this is not a practical solution since the typical purpose of creating a temp table is to carry that data along to other operations, but here those temp tables are only viable within their particular EXEC context, so a bit limited.