dropping a table in sql

[unoho]

hey y'all
so im having difficulties dropping a dynamic named table although it seems very trivial imo.

code:

while begin --some code utilizing the dynamic named table end --done w/ the table, delete it. exec(' IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N''[dbo].[' + @table_name + ']'') AND OBJECTPROPERTY(id, N''IsUserTable'') = 1) DROP TABLE [dbo].[' + @table_name + ']')

however it seems like it wont do the job and im stuck in my project. any clue what might be going wrong w/ this?

[Tony]

generate the string as it would be passed to the database. Execute the command manually. See what the database tells you.

[unoho]

it says "Command(s) completed successfully."

so i changed the drop table w/ print 'hello world' but it wouldn't print either. so im guessing the condition is false. although i cud clearly see the table in the database

[Tony]

So you could debug your conditionals, one element at a time...

you could also check if your DBMS supports

code:

DROP TABLE IF EXISTS @table_name

type of syntax.

[unoho]

ya it does...it's t-sql using sql server.. i think the problem is the path of the table. the way i coded it, it would save the whole path..but if i do a drop table, it only looks inside the current database.

so i ended up using replace function and got rid off the front directory..

probly wont make sense to u..but whtever, it works..somehow..

thnks though

[2goto1]

Did you have the right database selected? I.e. in SQL Server you can select a database from SSMS with the command

use MyDatabase;

You can issue a query from one database to another. I.e.

select * from MyDatabase.dbo.MyTable

Perhaps you were querying the system catalog from a different database

[Amailer]

If you are creating tables that are only required for a specific session, take a look at temporary tables:
http://techahead.wordpress.com/2007/09/27/sql-temporary-tables/

[unoho]

ya i was at the right database at that time. i wish i cud post the whole code but against company policy.
also, i tried temp tables before (like before) and it was good except i had few problems doing the exec commands.