I am trying to create a C# Method to DROP tables if they exist and to create if they do not.
So far I have created a SQL query which is TRYING to delete the tables if they exist.
However I am getting this error
System.Data.SqlClient.SqlException (0x80131904): Incorrect syntax near the keyword 'NULL'.
This is my code
static void CheckTablesExist()
{
try
{
// Build connection string
SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder
{
DataSource = "WIN10-LAP-HJP",
UserID = "sa",
Password = "Mypassword123",
InitialCatalog = "SAPExtract"
};
// Connect to SQL
Console.Write("Connecting to SQL Server ... ");
using (SqlConnection connection = new SqlConnection(builder.ConnectionString))
{
Console.WriteLine("Done.");
Console.WriteLine("Checking If table dbo.FolderInfo");
string queryCheckTable = "if object_id(@FolderTable, @FileTable) NOT NULL DROP TABLE dbo.FolderInfo ";
// string queryCheckTable = "DROP TABLE IF EXISTS @FolderTable";
using (SqlCommand command = new SqlCommand(queryCheckTable, connection))
{
command.Parameters.Add("@FolderTable", SqlDbType.NVarChar).Value = "dbo.FolderInfo";
command.Parameters.Add("@FileTable", SqlDbType.NVarChar).Value = "dbo.FileInfo";
connection.Open();
var result = command.ExecuteNonQuery();
// Check Error
}
}
}
catch (SqlException e)
{
Console.WriteLine(e.ToString());
}
}
You are receiving the syntax error because you are missing the word IS before NOT NULL. The following SQL should work:
if object_id(@FolderTable, @FileTable) IS NOT NULL DROP TABLE dbo.FolderInfo
User contributions licensed under CC BY-SA 3.0