This doesn't make any sense to me. I am trying to create a stored procedure that creates a temp table using T-SQL. The table will be created differently depending on the arguments passed. Here is an example of what I am trying to do:
DECLARE @.Switch bit
SET @.Switch = 0
IF @.Switch = 0
BEGIN
PRINT @.Switch
CREATE TABLE #DontWork (Zero int)
END
ELSE
BEGIN
PRINT @.Switch
CREATE TABLE #DontWork (One int)
END
SELECT * FROM #DontWork
If you run this as is, it fails stating that "There is already an object named '#DontWork' in the database." However if you comment out one of the CREATE TABLE statements (either one of them), it works fine. The PRINT @.Switch line will prove that the IF ELSE statement is evaluating properly if you change the value of @.Switch. My guess is that the parsing engine is throwing the error before it even tries to run the code. Is there any way to make this work the way it should? Or do I have to resort to creating 2 different tables and modifying the rest of my code to compensate for the change?
This is usually caused because you have ran a CREATE TABLE statement in a previous development iteration. Try appending this to the end of your code:
Code Snippet
go
drop table #DontWork
The temp table stays in scope after you run through one time so the next time through you get the error. Try hiliting the code I've given you and execute just the DROP TABLE. Then un-hilite the code and rerun query. It should run correctly once you have dropped the table.
OK, I'm all wet... Hang on.
You can alter it:
Code Snippet
create table #what (one int)
alter table #what
add two int
alter table #what
drop column one
select * from #what
go
drop table #what
/*
two
--
*/
The code doesn't execute.
The parsing engine is attempting to resolve the objects, and (incorrectly, in my opinion) assumes that the second instance of the create table is attempting to make a second object with the same name. The parsing engine is resolving objects, not checking logic and code flow.
To test, comment out EITHER CREATE statement and the code executes.
Your options include creating the #Temp table before the IF statement, or using a different #Table name in the second instance.
Or you could have both switched locations call out to another procedure that creates the #Temp table.
|||My code already involves altering the table. I was just trying to use an IF statement because one scenario creates a predictable table structure, and the other side requires that the field names be calculated at run time. I was trying to save myself some effort by simply having that CREATE TABLE command in there twice, but it seems that because of what Arnie said about the parsing engine resolving objects, not checking logic and code flow, I'm going to have to do things the complicated way. I wish there was a way to communicate things like this to the powers that be at Microsoft. Any idea how to do that, if at all possible?|||They do pay attention to the suggestions.
Suggestions for SQL Server
http://connect.microsoft.com/sqlserver
|||MS SQL has always had a problem with this construct. The solution, as mentioned, is to create the table once, and then use alter table to change the table to what you want. Or just create 2 tables of different names.|||
SQL Server compiles the entire batch (SP, trigger, function or ad-hoc) and compilation doesn't take into account run-time information (variable values, control of flow etc). This gets tricky for temporary tables because of the way they are scoped. For best performance and manageability, you should put the creation logic for the different conditions in their own SPs and the execution logic too. This provides better reusability. You could use the ALTER TABLE approach but that will give bad performance in SQL Server 2005 since it negates the caching that we do automatically on temporary tables (metadata & 1 page of allocation which can get reused). Of course, if you can remove the temporary tables altogether.
Btw, your code will work if you were creating a permanent table conditionally.
