sql server conditional select into temp table

Question

I'm working in SQL Server 2008, and I'm trying to select into a temp table based on a certain condition...for a report, I need up to 18% of the records to be of a certain product type.

if ((@totalRecords * .18) > @productTypeCount)
    select * into #tmpLP_REIT
    from myTable where productType = @productType
else
    select top 18 percent * into #tmpLP_REIT
    from myTable where productType = @productType

I keep getting error "there is already an object named '#tmpLP_REIT' in the database" even though I know it isn't there because when I try to drop such table, it points out it doesn't exist. I also get this error on the else clause.

It seems it's ignoring my if statement and going right through creating the first table, then trying to create it again. any suggestions?

Answer 1

Is this code in a loop?.. that would explain the error if it were happening the second time though. If not, the syntax checker must just be choking because it sees 2 queries creating the same table, not realizing it is a conditional.

One solution would be to create the table first, and then change the syntax of those 2 queries to insert into queries.

Another solution would be to use dynamic SQL... build the right query in the conditional, and then execute it afterwards. This should get passed the validation you're currently triggering.