2

Following the guidelines presented in SO Question #167576, I constructed the following SQL query string in my C# WinForm application to determine if table RMCoil exists:

using (SqlCeCommand cmd = new SqlCeCommand(null, new SqlCeConnection(Connection))) {
  cmd.Connection.Open();
  cmd.CommandText =
    "IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME='RMCoil') " +
    "SELECT 1 ELSE SELECT 0";
  if (cmd.ExecuteNonQuery() < 1) {
    // code to create table
  }
}

Executing the non query throws this SqlCeException:

There was an error parsing the query. [ Token line number = 1,Token line offset = 1,Token in error = IF ]

I'm fairly new to SqlCeServer. Could someone enlighten me on how to execute this query all in one line?

Community
  • 1
  • 1

2 Answers2

3

SELECT EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME='RMCoil') might do the trick?
You would also have to do ExecuteScalar() instead of ExecuteNonQuery() I think to retrieve your value...

As Paul Sasik very wisely pointed out, the following query is even better:
SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME='RMCoil'

Mr47
  • 2,655
  • 1
  • 19
  • 25
  • 1
    +1 but could be simpler. If you use `Execute Scalar` then your query could be: `SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME='RMCoil'` If you get 0 then there's no such table. If you get 1 then yes. – Paul Sasik Jun 15 '11 at 19:02
  • 1
    @Paul Very true. I'll add that to my answer. – Mr47 Jun 15 '11 at 19:03
  • [+1] Ugh! I'd thought about `ExecuteScalar()` when I originally wrote the code, but thought I'd try `ExecuteNonQuery()` first. When I got the error, I forgot! –  Jun 15 '11 at 19:09
  • Actually, Paul, `SELECT COUNT(*)...` returns 1 every time, it's just all values are `NULL`. –  Jun 15 '11 at 19:12
  • 2
    **This works!** `"SELECT TABLE_SCHEMA FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME='RMCoil'"` –  Jun 15 '11 at 19:16
0

This actually made a better test:

SELECT CASE WHEN EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME='RMCoil') THEN 1 ELSE 0 END