Programming C, C++, Java, PHP, Ruby, Turing, VB
Computer Science Canada 
Programming C, C++, Java, PHP, Ruby, Turing, VB  

Username:   Password: 
 RegisterRegister   
 dropping a table in sql
Index -> General Programming
View previous topic Printable versionDownload TopicSubscribe to this topicPrivate MessagesRefresh page View next topic
Author Message
unoho




PostPosted: Wed May 25, 2011 1:56 pm   Post subject: dropping a table in sql

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?
Sponsor
Sponsor
Sponsor
sponsor
Tony




PostPosted: Wed May 25, 2011 2:11 pm   Post subject: RE:dropping a table in sql

generate the string as it would be passed to the database. Execute the command manually. See what the database tells you.
Latest from compsci.ca/blog: Tony's programming blog. DWITE - a programming contest.
unoho




PostPosted: Wed May 25, 2011 2:46 pm   Post subject: RE:dropping a table in sql

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




PostPosted: Wed May 25, 2011 2:51 pm   Post subject: RE:dropping a table in sql

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.
Latest from compsci.ca/blog: Tony's programming blog. DWITE - a programming contest.
unoho




PostPosted: Wed May 25, 2011 3:08 pm   Post subject: RE:dropping a table in sql

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




PostPosted: Wed May 25, 2011 3:22 pm   Post subject: RE:dropping a table in sql

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




PostPosted: Wed May 25, 2011 5:22 pm   Post subject: RE:dropping a table in sql

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




PostPosted: Wed May 25, 2011 9:26 pm   Post subject: RE:dropping a table in sql

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.
Sponsor
Sponsor
Sponsor
sponsor
Display posts from previous:   
   Index -> General Programming
View previous topic Tell A FriendPrintable versionDownload TopicSubscribe to this topicPrivate MessagesRefresh page View next topic

Page 1 of 1  [ 8 Posts ]
Jump to:   


Style:  
Search: