Drop Table List 1

[CarpalT]

I'm just learning how to write SQL queries. Reading manuals a& help like crazy but sometimes that doesn't help.
I have this, that works:

IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = 'Sam')
DROP TABLE Sam

Can I modify it to something like - -

IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME IN (Sam, Fred)
DROP TABLE Sam
DROP TABLE Fred

Results at the moment: Incorrect syntax near the keyword 'DROP'.
Thanks in advance for any help!

The world is full of good people.

 

[rac2]

Sam and Fred are strings, they need single quote marks.

IF is part of Transact SQL, not SQL, it is not used in queries. You are writing a procedure not a query.

Good practice with an IF statement is to explicitly mark the beginning and ending of the statements to be executed with BEGIN and END, even where there is a single statement.

And then there is that pesky syntactic requirement that parentheses be balanced.

IF EXISTS(
      SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
      WHERE TABLE_NAME IN ('Sam', 'Fred')
          )
BEGIN
   DROP TABLE Sam
   DROP TABLE Fred
END

Note that this code will try to drop Fred whether the table exists or not.


The world is full of good programmers. Why not hire one.

 

[mrdenny]

While yes you can do this as rac2 has shown, the better method would be to have two IF EXISTS statements, since there is no guarantee that both tables will be there if one is there.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[CarpalT]

Thank you both, I'll take a look at that.
Sure, I'll just go tell my boss to hire a programmer.

The world is full of good people.

 

[nomadicalloy]

you can also use following

IF OBJECT_ID('FRED') IS NOT NULL
DROP TABLE FRED



SQL Help Blog

http://www.xpherion.com/blog.asp

 

[CarpalT]

Many thanks to all of you, and thank you for the SQL help blog link. I'm sure I can learn a lot from it.




The world is full of good people.