if exists function in MS SQL

[yahoo182]

Hi there,
I am currently porting a code form Mysql to MSSQL,
In mysql the following sql statement works fine:

"DROP TABLE IF EXISTS "+tableName

but when I run it against MSSQL, they are saying that they dont allow an IF there:

Does anyone know how I should go about implementing this?

Thanks

 

[evaleah]

if exists (select * from dbo.sysobjects where id = object_id(N'[tableName]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [tableName]
GO

 

[Pattycake245]

if exists(select table_name from INFORMATION_SCHEMA.TABLES where table_name = <tablename>)
begin
  DROP TABLE <tablename>
end

Tim

 

[yahoo182]

Hi there,
Thanks for the quick feedback.
Say if I wanted to put this in a string variable, how would I go about doing this?

So far I have

dbcon = new SqlConnection(connectionString);
dbcon.Open();
IDbCommand dbcmd = dbcon.CreateCommand();

string sql = "if exists(select table_name from INFORMATION\
_SCHEMA.TABLES where table_name = <tablename>)
begin
DROP TABLE <tablename>
end
";
dbcmd.CommandText = sql;
dbcmd.ExecuteNonQuery();

 

[yahoo182]

and when I execute that code, I get the following error..

Unhandled Exception: System.Data.SqlClient.SqlException: Line 1: Incorrect syntax near '<'.
Line 1: Incorrect syntax near '<'.
at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
at Test.Main()

 

[jbenson001]

string sql = "if exists(select table_name from INFORMATION\
_SCHEMA.TABLES where table_name =" + tablevariable + ") +
begin
DROP TABLE <tablename>
end
";

 

[yahoo182]

humm it seems to give me this newline in constant error if I have new lines within the string definition....

addImagesMssqlDB.cs(21,21): error CS1010: Newline in constant
addImagesMssqlDB.cs(26,1): error CS1010: Newline in constant

 

[jbenson001]

if you want to split up the string onto many lines, you will need to use the line continuation character and put quotes around each new line.

 

[yahoo182]

after going through some websites, I think it should be
string sql = "if exists(select table_name from INFORMATION_SCHEMA.TABLES where table_name = " + tableName + ") DROP TABLE " +tableName ;


but still I get an error saying that

if exists(select table_name from INFORMATION_SCHEMA.TABLES where table_name = images) DROP TABLE images

Unhandled Exception: System.Data.SqlClient.SqlException: Invalid column name 'images'.
at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
at Test.Main()

 

[jbenson001]

put quotes around images... 'images'

 

[yahoo182]

Actually I tried that already as follows but It still gives me the same result.

if exists(select table_name from INFORMATION_SCHEMA.TABLES where table_name='images') DROP TABLE 'images'

Unhandled Exception: System.Data.SqlClient.SqlException: Line 1: Incorrect syntax near 'images'.
at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
at Test.Main()

 

[vongrunt]

DROP TABLE images

Without single quotes.

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)