Existentialist SQL


  • Garbage Person

    I am going tu type this on a mobile keyboard because it is just so glorious it cannot wait till I get home.

    (@value is a varchar initialized null and has an int selected into it, meaning the only possible values are a stringy int and null)

    IF (ISNULL(@value, '') <> '')



  • Someone has been burned and is now overcautious. Consider if it were written this way (reversal of truth):

    IF (ISNULL(@value,'') = '')

    In that variation, the ISNULL is required for an ANSI NULL compliant SQL. Comparison predicates where either side is NULL are treated as undefined, so that if @value is NULL then @value = '' should be undefined; neither false nor true.

    Since the target of the IF is executed only if the logical result is true, then the target would be executed only if @value contained exactly '' (empty string).

    That is, @value of NULL or '123' wuld produce exactly the same behavior, which is a bit counter intuitive.

    This is further complicated in some databases like Access and, in certain modes, SQL Server and Oracle, which treat the empty string '' as equivalent to NULL. (Which it really should not be.)

    If @value is truly either NULL or an int string, then I would test it this way (for databases that support IS NULL and IS NOT NULL) :

    IF (@value IS NOT NULL)



  • @CoyneTheDup said:

    some databases like Access

    Ahem..


  • BINNED

    @CoyneTheDup said:

    which treat the empty string '' as equivalent to NULL. (Which it really should not be.)

    🍿



  • @swayde said:

    Ahem..

    Well, yes, but they call it a "database"...and one can do useful things with it.


Log in to reply