Why does SQL Server thinks a Temp Table already ex

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?

Answer 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

Answer 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]

Answer 3

since i dont have sql 2008, i cannot test it. however as far as i know, this is a parser issue explicitly with temp tables. the same would work fine with normal tables

to sort this out, just split your code with logical GO statements

ideally: check for existence of temp tables before you create your temp tables, drop them if they exist, fire a go, do the creation of temp tables again, fire a go, then any post processing, fire a go, finally drop them again and fire the last go

---

you might also want to try and use the table variable instead of temp tables if above does not solve it for you

Answer 4

You could always "cheat":

DECLARE @SQL VARCHAR(200)

IF (1=1)
BEGIN
    SET @SQL = 'CREATE TABLE #Temp     ' +
               '(                      ' +
               '    a BIGINT NOT NULL, ' +
               '    b BIGINT NOT NULL  ' +
               ')                      '
END
ELSE
BEGIN
    SET @SQL = 'CREATE TABLE #Temp     ' +
               '(                      ' +
               '    a BIGINT NOT NULL, ' +
               '    b BIGINT NOT NULL, ' +
               '    c BIGINT NOT NULL  ' +
               ')                      '
END

EXEC SP_EXECUTESQL @SQL