Why does SQL Server think that Temp Table already exists when not?

Question

Reference Information. A procedure is stored there that "populates" the temporary table with the given name. The procedure is general in that it checks the schema of the temp table and then does different “things” depending on the schema. I understand that this is a little strange, but I do not want to change it, because everything works fine in most situations except ....

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

So this SQL crashes:

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
An object already exists with the name '#test' in the database.

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

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

Answer 1

This is a limitation. Dynamic SQL will not work because #tmp will be created in a new session and will be lost immediately. For the EXACT fragment, 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 in the same batch .. #name, 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 the full name. For example,

 [tempdb].[dbo].[temptable] 

I found out this little trick here Paste the result of a dynamic query into a table .

Intellisense will complain, but you can still create or modify the stored procedure.

Upon completion, be sure to discard it:

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

Answer 3

since I do not have sql 2008, I can not test it. however, as far as I know, this is a parser problem explicitly with temporary tables. the same will work fine with regular tables

to sort this, just share your code with the logical booleans of GO

Ideally: check for temporary tables before creating temporary tables, discard them if they exist, run go, create temporary tables again, run go, then any post-processing, start, finally lower them again and run the last run

---

you can also try using a table variable instead of temp tables if the above doesn't solve it for you

Answer 4

You can 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