Odd error using IF/ELSE IF statements

Question

I'm trying to create a temp table dependent on the value of a scenario parameter and using the following IF statement but getting the error below:

    IF @indexName = 'A'
        begin select top 400 * into #temp from #pretemp order by EMRev desc end
    ELSE IF @indexName = 'B'
        begin select top 75 * into #temp from #pretemp order by EMRev desc end
    ELSE IF @indexName = 'C'
        begin select top 300 * into #temp from #pretemp order by EMRev desc end
    ELSE 
        begin select top 100 * into #temp from #pretemp order by EMRev desc end

Msg 2714, Level 16, State 1, Line 179 There is already an object named '#temp' in the database. Msg 2714, Level 16, State 1, Line 181 There is already an object named '#temp' in the database. Msg 2714, Level 16, State 1, Line 183 There is already an object named '#temp' in the database.

I'm certain the IF statement works based on the @indexName variable (replacing the block statement with something simple (eg, 'select @indexName'), the program runs fine).

Any ideas about what's causing this error?

Answer 1

The SQL parser looks for all places that might create a temp table, and only allows a given temp table name to be used ONE time. The solution is to do something like this:

select * into #temp from #pretemp where 1=2

IF @indexName = 'A'
    begin insert into #temp select top 400 * from #pretemp order by EMRev desc end
ELSE IF @indexName = 'B'
    begin insert into #temp select top 75 * from #pretemp order by EMRev desc end
ELSE IF @indexName = 'C'
    begin insert into #temp select top 300 * from #pretemp order by EMRev desc end
ELSE 
    begin insert into #temp select top 100 * from #pretemp order by EMRev desc end

The where 1=2 creates the table structure with zero records... Then the if statements populate the temp table.

Cheers!

Answer 2

Since only the top number of records are difference. You can try this

declare @num int 

SET @num = CASE @indexName 
                    WHEN 'A' THEN 400
                    WHEN 'B' THEN 75
                    WHEN 'C' THEN 300
                    ELSE  100
            END

select top (@num) * into #temp from #pretemp order by EMRev desc