I ran across this problem earlier today, and thought I should address this here in case anyone is interested in my solution.
When doing a SELECT INTO command against the same temp table in both IF and ELSE blocks, you will quickly learn that SQL Server complains that there “is already an object named ‘#temp’ in the database”. What one can do to avoid this problem is declare the temp table before the IF/ELSE block and insert into the table afterwards, but what if you want to use SELECT INTO instead?
if @var = 1 select 'abc' into #temp; else select 'def' into #temp; select * from #temp;
This was my solution:
declare @temp_table varchar(25); if @var = 1 begin select 'abc' into #temp1; set @temp_table = '#temp1'; end else begin select 'def' into #temp2; set @temp_table = '#temp2'; end exec('select * from ' + @temp_table);
Fairly easy code, basically you’re creating one of the two possible temp tables to cover both blocks.
Initially, I figured I’d use the exec command (or more commonly known as EXECUTE) to create the temp table using SELECT INTO, but I found out that you cannot create the temp table inside the dynamic SQL as the rest of the procedure won’t be able to see it due to it being outside of scope. But it works vice versa where you can create the temp table inside the proc and then have access to it inside the dynamic SQL.