SQL Server Compact Edition Isnull(Sth, ' ') Returns a Boolean Value

SQL Server Compact Edition ISNULL(sth, ' ') returns a boolean value?

According this ISNULL is not implemented in SQL Server CE. I would expect to see a syntax error raised, however.

Workaround: you can use CASE WHEN email IS NULL THEN 'eeee' ELSE email END or COALESCE. Preferably the latter.

Also use parameterised queries. If you don't know why you should, learn.

How to make ISNULL not just return true or false

You need to use coalesce http://technet.microsoft.com/en-us/library/ms174075.aspx

The syntax is the same as the isnull.

SQL Server Compact 4.0 with Entity Framework Conditional String Comparison

Yeah, apparently ISNULL is funky in Compact.

You could do it on the client, though:

var transactions = description == null ?
                    entities.Transactions.Where(t.Description == null)
                    : entities.Transactions.Where(t.Description == description);

...and I suspect no ISNULL will be generated in this case.

Which MySQL data type to use for storing boolean values

For MySQL 5.0.3 and higher, you can use BIT. The manual says:

As of MySQL 5.0.3, the BIT data type is used to store bit-field
values. A type of BIT(M) enables storage of M-bit values. M can range
from 1 to 64.

Otherwise, according to the MySQL manual you can use BOOL or BOOLEAN, which are at the moment aliases of tinyint(1):

Bool, Boolean: These types are synonyms for TINYINT(1). A value of
zero is considered false. Non-zero
values are considered true.

MySQL also states that:

We intend to implement full boolean
type handling, in accordance with
standard SQL, in a future MySQL
release.

References: http://dev.mysql.com/doc/refman/5.5/en/numeric-type-overview.html

SQL Server 2008 IIF statement does not seem enabled

As noted, IIF is a SQL2012 feature.

Replace your IIF with a CASE ( Which is what SQL 2012 would do anyway )

 SELECT CASE field WHEN '' THEN 'ONe action' ELSE 'Another' END

How to filter using WHERE with a parameter possibly beeing null

The best solution is a constraint on the table that prevents duplicates from going into the table. You can put one in with a unique index:

create unique index idx_MailRecipientAddress_address_name on MailRecipientAddress(Address, Name);

This will generate an error on the insert, which you would then need to catch.

However, this is only a partial solution, because NULL values do not count as duplicates. You might solve your overall problem by not allowing NULL values in the field at all. Instead, represent no data using empty strings. Note: I wouldn't normally recommend this. In SQL, NULL means "unknown" and by the definition of the language, two "unknown" values are not equal. However, you seem to want them to be equal.

As for SQL, yours is okay, but it equates NULL and the empty string. An explicit check is more accurate:

WHERE (Address = @Address or Address is null and @Address is null) and
      (Name = @Name or Name is null and @Name is null)

NULL values not filtered out with WHERE statement

use this for only want null recode

SELECT ID, VOLUME,  TYPEOF(VOLUME) FROM DBT.BASE WHERE VOLUME IS NULL
or
SELECT ID, VOLUME,  TYPEOF(VOLUME) FROM DBT.BASE WHERE ISNULL(VOLUME,'') = ''

if you get not null value then use

SELECT ID, VOLUME,  TYPEOF(VOLUME) FROM DBT.BASE WHERE ISNULL(VOLUME,'') <> ''
or 
SELECT ID, VOLUME,  TYPEOF(VOLUME) FROM DBT.BASE WHERE VOLUME IS NOT NULL

Check if MySQL table exists without using select from syntax?

If you want to be correct, use INFORMATION_SCHEMA.

SELECT * 
FROM information_schema.tables
WHERE table_schema = 'yourdb' 
    AND table_name = 'testtable'
LIMIT 1;

Alternatively, you can use SHOW TABLES

SHOW TABLES LIKE 'yourtable';

If there is a row in the resultset, table exists.

---

Related Topics