ERROR: There is already an object named '#temp' in the database

Question

I want to include a similar code to the one below in a stored procedure. But when I execute it, I get this error:

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

Code:

IF 1=1
    SELECT
        [col1] = 'inside IF 1',
        [col2] = 'inside IF 2'
    INTO
        #temptable
ELSE
    SELECT
        [col1] = 'inside Else 1',
        [col2] = 'inside Else 2',
        [col3] = 'inside Else 3'
    INTO
        #temptable

Is there a way to solve this? Please note the ELSE block has 3 columns while the IF block has 2 columns going into the #temptable

Answer 1

Like Larnu already said, this is a parsing error. It sees you trying to create the same table multiple times and doesn't know that only one of them can be reached.

Depending on how this temp table is being used there might be better ways to refactor the code but working from what was provided you have a couple of options:

1. Use dynamic queries to hide the SQL from the parser. Dynamic queries are generally frowned upon and should be avoided when possible. (This has been updated to use a global temp table to work around the scope issue)

IF 1=1
  EXEC('SELECT
    [col1] = ''inside IF 1'',
    [col2] = ''inside IF 2''
  INTO
    ##temptable')
ELSE
  EXEC('SELECT
    [col1] = ''inside Else 1'',
    [col2] = ''inside Else 2'',
    [col3] = ''inside Else 3''
  INTO
    ##temptable')

2. If you know ahead of time all of the column names then you can CREATE the #temptable with all possible columns, and in your conditional logic you can use ALTER TABLE to remove unused columns.

CREATE TABLE #temptable (
    col1    varchar(10),
    col2    varchar(10),
    col3    varchar(10)
)

IF  1 = 1
BEGIN
    INSERT  INTO #temptable (col1, col2)
    SELECT
        col1 = 'inside IF 1',
        col2 = 'inside IF 2'

    ALTER TABLE #temptable DROP COLUMN col3
END
ELSE
BEGIN
    INSERT  INTO #temptable (col1, col2, col3)
    SELECT
        col1    = 'inside Else 1',
        col2    = 'inside Else 2',
        col3    = 'inside Else 3'
END