Query optimization

Question

I have an stored procedure of this format

if(condition 1)
begin
(
  -----
  -----
  select into #temp1
  ----
  drop #temp1
)
end
if(condition 2)
begin
(
  -----
  -----
  select into #temp1
  ----
  drop #temp1
)
end

now when the above stored procedure is execute it shows me an error that:

"There is already an object named '#temp1' in the database."

When I modify the stored procedure like,

if(condition 1)
begin
(
  -----
  -----
  select into #temp1
  ----
  drop #temp1
)
end
if(condition 2)
begin
(
  -----
  -----
  select into #temp2
  ----
  drop #temp2
)
end

It works good.But I want to optimize this because of creating too many temporary tables.

Can anybody help me on this?

Answer 1

You can drop the table at the start of the procedure. It's a bit tricky, but you can check for the existence of a temporary table like:

if object_id('tempdb..#temp1') is not null
    drop table #temp1

Answer 2

I'd create/drop the temp table outside of the conditional statement like this:

create table #temp ...

if(condition 1)
begin
(
  -----
  -----
  select into @temp
  ----
)
end
if(condition 2)
begin
(
  -----
  -----
  select into @temp2
  ----
)
end

drop table #temp

Assuming your version of SQL Server supports them, and you are aware of the differences in terms of logging and transactional rollback, it might be better to use a table variable. That way you don't have to worry about it not being dropped once it goes out of scope.

Answer 3

You could try adding TRUNCATE TABLE #temp1 immediately before if(condition 2) in the first layout, but this question and accepted answer implies that a table variable will perform better.