Why does SQL Server thinks a Temp Table already exists when it doesn't?

Question

Background: There's a stored procedure which does "stuff" with a temp table of a given name. The procedure is generic in that it inspects the schema of the temp table and then does different "stuff" depending on the schema. I understand that this is a little weird but I'm reluctant to change it because it all works fine in most situations, except....

If I have a stored procedure which creates two different schemas for a temp table with the same name. Logically it only creates one temp table depending on which branch of the IF. The problem is that when the Sproc is checked by SQL Server it seems like it is evaluating both sides of the IF (which makes sense if it's checking the SQL syntax.)

So this SQL fails:

IF (1=1)
BEGIN
    CREATE TABLE #test
    (
        a BIGINT NOT NULL,
        b BIGINT NOT NULL
    )
END 
ELSE
BEGIN
    CREATE TABLE #test
    (
        a BIGINT NOT NULL,
        b BIGINT NOT NULL,
        c BIGINT NOT NULL   
    )   
END

--exec SomeProcedureWhichDoesStuffWith#Test

DROP TABLE #test 

with the following error:

Msg 2714, Level 16, State 1, Line 14
There is already an object named '#test' in the database.

No combination of drop table inside the ifs (before or after the create table DDL) seems to satisfy the sql checker.

Any ideas how I can do this? Can I, for example, tell SQL to not perform the syntax check and just accept the sproc as is?

Solution 1

It's a limitation. Dynamic SQL won't work either since #tmp will be created in a new session and immediately lost. For the EXACT snippet as shown, this does the same

CREATE TABLE #test
(
    a BIGINT NOT NULL,
    b BIGINT NOT NULL
)

IF not (1=1)
    ALTER TABLE #test ADD c BIGINT NOT NULL   

There cannot be two CREATE .. #name within the same batch, but this will also work in general form

IF (1=1)
BEGIN
    CREATE TABLE #test
    (
        a BIGINT NOT NULL,
        b BIGINT NOT NULL
    );
END 
GO

IF NOT (1=1)
BEGIN
    CREATE TABLE #test
    (
        a BIGINT NOT NULL,
        b BIGINT NOT NULL,
        c BIGINT NOT NULL   
    )
END

Solution 2

Instead of #test, use a fully qualified name. For example,

[tempdb].[dbo].[temptable]

I learned this little trick here Insert result of executing dynamic query into a table .

Intellisense will complain but you will still be able to create or alter the stored procedure.

When done with it, be sure to drop it:

DROP TABLE [tempdb].[dbo].[temptable]

Comments

[Daniel James Bryars]

Yes thanks for the information. Unfortunately it's creating the tables in a sproc, so it's got to be in the same batch (so it's a no go on GO).

[Martin Smith]

That won't work as it would go out of scope immediately and not be usable in the outer script It would need to be a global temp table or all of the usages of the temp table would need to be in the dynamic SQL.