And, finally, here's the text of the explanation. Simply put, creating temp tables in if/else blocks confuses the parser.
PROBLEM DESCRIPTION
=====================
Customer is trying to create temp tables and populate the tables with criteria
-- it gives a error msg
ERROR MESSAGE
==============
Server: Msg 2714, Level 16, State 1, Procedure test, Line 9
There is already an object named '#temp' in the database.
Resolution:
=======================================================================
-- Its a creation of temp table inside if else and parser is not able to make out that the table does not exist
-- You referred to KB Creating New Database Objects Fails in a SQL Server Database (827448)
-- The requirement is to populate the table on the basis of conditions
-- referred to PRB: Creating Temp Table Inside IF and ELSE Conditions Gives Error 2714 (295305)
-- We tried WITH RECOMPILE
-- This talks about the same issue we are running into
-- It talks about the work around to create the tables outside the If else and alter it inside
-- Since this would not fit his requirement
-- Suggested that we do a select into with criteria 1=2 so that it creates a Empty table and then in If else for insert into with the criteria.
-- You did not want to create stored procedures since the first one is relatively easier
-- After we modified the stored Procedure it worked Fine.
References
============================
For the error we were getting this is one of the documents that you could refer to: Following KB articles may be of help with regards to this topic.
» 295305 PRB: Creating Temp Table Inside IF and ELSE Conditions Gives Error 2714
» 305977 INF: Frequently Asked Questions - SQL Server 2000 - Table Variables
» 44519 INF: Error Handling in Transact-SQL Blocks and Stored Procedures
(With The explanation in this Kb article, we can correlate with the below limitation)
» Books Online : Transact-SQL Tips mentions this too as a limitation of Use of the temporary tables.
Minimizing the Use of Temporary Tables
Minimize the use of temporary tables as places to store intermediate results in a series of Transact-SQL statements. Some logic is too complex to perform in a single Transact-SQL statement. In these cases, you must code multiple Transact-SQL statements and use temporary tables to pass the results of one statement to the next. Creating and maintaining the temporary tables requires overhead; if possible, consider coding the operation as a single, more complex Transact-SQL statement.
In SQL Server 2000, use of temporary tables in stored procedures and triggers may cause the stored procedure or trigger to be recompiled every time it is used. To avoid such recompilation, stored procedures or triggers that use temporary tables must meet the following requirements:
In the stored procedure or trigger, all statements that contain the name of a temporary table must refer to a temporary table created in the same stored procedure. The temporary table cannot have been created in a calling or called stored procedure, or in a string executed using EXECUTE or sp_executesql.
All statements that contain the name of a temporary table must appear syntactically after its creation in the stored procedure or trigger.
The stored procedure or trigger cannot contain any DECLARE CURSOR statement whose SELECT statement references a temporary table.
All statements that contain the name of any temporary table must precede any DROP TABLE statement that references a temporary table. DROP TABLE statements are not needed for temporary tables created in a stored procedure; the tables are dropped automatically when the procedure terminates.
Statements creating a temporary table (such as CREATE TABLE or SELECT INTO) may not appear in a control-of-flow statement such as IF...ELSE or WHILE.
This is by design, and there can be only work around for this. How ever there are not many Knowledgebase articles with explanation. We thank you for bringing this to our notice. I will pass this on as a feedback and see that there is enough information available through KB articles too.
Phil Hegedusich
Senior Programmer/Analyst
IIMAK
-----------
I'm not as think as you confused I am.
-----------
Flabbergasted (a.): Amazed at how much weight one has gained.
-----------
Oyster (n.): One who sprinkles their conversation with Yiddish expressions.