>Howdy, bro :)
>
>The compiler doesn't differentiate between the two parts of the IF..ELSE, so it has presumed the creation of the temp table in the IF before trying to compile the ELSE where it assumes another table creation, hence the error.

Yep, that's what I was trying to work around. I didn't know the compiler was going to do that. <g> Actually, I only get to this part in the code if the temp table wasn't created by a previous call into the connection, which will be the case in most instances. I already had a prior check for the temp table's existence, as suggested by Sergey B. If it isn't there, this chunk of code gets run.

>
>Prior to the IF..ELSE, you can either:
>- Create the temp table using CREATE TABLE... (since you probably already know the structure)
>- If you don't necessarily know (or need to know) the structure, you can SELECT TOP 0 * INTO... the temp table.
>
>and then in the IF, do an INSERT INTO the temp table
>
>FWIW: You'll typically get better performance with a CREATE TABLE, because some sys tables get locked with SELECT INTO.

Thanks, I'll try that approach. The table schema is the same in both IF and ELSE sections of code, so that should work just fine. And thanks for the tip about the sys tables getting locked - I'll avoid that whenever I can! I haven't read far enough in my SQL books to discover that tip!

- Bruce Allen

>
>
>HTH
>
>>I am creating a temp table inside a store proc. Depending on whether an input
>>parameter was passed, I run different code to create the temp table.
>>
>>The SQL compiler gives me this error when I try to run the code:
>> There is already an object named #SelectedCustomers in the database.
>>
>>What am I doing wrong here?
>>
>>(This is a simplification of what I'm really working with)
>>
>>-- @tcState is an input parameter to the stored proc, and is in scope.
>>IF @tcState IS NOT NULL BEGIN
>> SELECT kCustomer
>> INTO #SelectedCustomers
>> FROM Customers
>> WHERE cState = @tcState
>>END
>>ELSE BEGIN
>> SELECT kCustomer
>> INTO #SelectedCustomers
>> FROM Customers
>>END