I have an error while trying to run a simple SQL statement!

[Guest_imported]

Well, the problem is: I run the statement below:
if not exists (select * from sysobjects where name = 'temptable')
BEGIN
create table temptable(name int)
END
ELSE
BEGIN
SELECT * FROM temptable
END
when I run it the first time, there are no problems, but the second time I run it, I receive the error:
Msg 2714, Level 16, State 1
There is already an object named 'temptable' in the database.
Is there a solution?

 

[Guest_imported]

Nothing is wrong with your statemnt.
It seems like you need to reboot your server. It happened with us also, and after rebooting everything was OK.
Did we find a new bug in SQL server?

 

[bigscouse]

You are creating a table called temptable so the next time you run you are trying to create a table that you have already created.
You need to drop the table when the querry finishes.

Ged Jones

Top man

 

[grega]

This looks like Sybase (but I'm sure other DB's are the same) ... the problem is (from the manual)

When a create table or create view command occurs within an if...else block, Adaptive Server creates the schema for the table or view before determining whether the condition is true. This may lead to errors if the table or view already exists.

Greg.