+ Reply to Thread
Page 1 of 4 1 2 3 ... LastLast
Results 1 to 10 of 31

True and False in T-SQL

  1. True and False in T-SQL

    I'm trying to write some code.

    I've got two variables defined as bit, @Exist_Recent and @Exist_Old.


    I want to use them as booleans. I'm populating them with the record
    counts from two different queries.

    I want to be able to say:

    If @Exists_Recent and not @exists_old
    blah blah blah

    It doesn't like that "not". Instead I've had to say:

    If @Exists_Recent = 1 and @Exists_Old = 0

    Is this the way to do it?

    If I get two records back, with a binary image of "0010", will the bit
    field get "1", or "0"?




  2. Re: True and False in T-SQL

    On 13 Jul, 18:26, doofy wrote:
    > I'm trying to write some code.
    >
    > I've got two variables defined as bit, @Exist_Recent and @Exist_Old.
    >
    > I want to use them as booleans. I'm populating them with the record
    > counts from two different queries.
    >
    > I want to be able to say:
    >
    > If @Exists_Recent and not @exists_old
    > blah blah blah
    >
    > It doesn't like that "not". Instead I've had to say:
    >
    > If @Exists_Recent = 1 and @Exists_Old = 0
    >
    > Is this the way to do it?
    >


    That's right. BIT is a numeric type not a boolean. SQL Server doesn't
    have a boolean type that you can use for variables and columns even
    though boolean expressions are valid in places (IF and WHERE for
    example).


    > If I get two records back, with a binary image of "0010", will the bit
    > field get "1", or "0"?


    I'm not certain what you mean by this. If you assign any numeric or
    binary value to a BIT other than zero or null then the result is 1.

    --
    David Portas, SQL Server MVP

    Whenever possible please post enough code to reproduce your problem.
    Including CREATE TABLE and INSERT statements usually helps.
    State what version of SQL Server you are using and specify the content
    of any error messages.

    SQL Server Books Online:
    http://msdn2.microsoft.com/library/m...S,SQL.90).aspx
    --


  3. Re: True and False in T-SQL

    On Jul 13, 1:26 pm, doofy wrote:
    > I'm trying to write some code.
    >
    > I've got two variables defined as bit, @Exist_Recent and @Exist_Old.
    >
    > I want to use them as booleans. I'm populating them with the record
    > counts from two different queries.
    >
    > I want to be able to say:
    >
    > If @Exists_Recent and not @exists_old
    > blah blah blah
    >
    > It doesn't like that "not". Instead I've had to say:
    >
    > If @Exists_Recent = 1 and @Exists_Old = 0
    >
    > Is this the way to do it?


    NOT works, but not quite like that. Here's how it can work

    If @Exists_Recent = 1 AND NOT (@Exists_Old = 1)

    Another way:

    If @Exists_Recent = 1 AND @Exists_Old != 1


    As David said, BIT is a numeric type, not a boolean. Just like an
    "int" is four bytes wide, or 32 bits, with a maximum value of
    +2147483647, a BIT is one bit wide, with a maximum value of one. In
    fact, BIT is the only unsigned numeric type in SQL Server -- all
    others are signed.


  4. Re: True and False in T-SQL

    > In fact, BIT is the only unsigned numeric type in SQL Server
    > -- all


    Except tinyint, which stores integer data from 0 to 255.

    RLF



  5. Re: True and False in T-SQL

    rpresser wrote:
    > On Jul 13, 1:26 pm, doofy wrote:
    >> I'm trying to write some code.
    >>
    >> I've got two variables defined as bit, @Exist_Recent and @Exist_Old.
    >>
    >> I want to use them as booleans. I'm populating them with the record
    >> counts from two different queries.
    >>
    >> I want to be able to say:
    >>
    >> If @Exists_Recent and not @exists_old
    >> blah blah blah
    >>
    >> It doesn't like that "not". Instead I've had to say:
    >>
    >> If @Exists_Recent = 1 and @Exists_Old = 0
    >>
    >> Is this the way to do it?

    >
    > NOT works, but not quite like that. Here's how it can work
    >
    > If @Exists_Recent = 1 AND NOT (@Exists_Old = 1)
    >
    > Another way:
    >
    > If @Exists_Recent = 1 AND @Exists_Old != 1
    >
    >
    > As David said, BIT is a numeric type, not a boolean. Just like an
    > "int" is four bytes wide, or 32 bits, with a maximum value of
    > +2147483647, a BIT is one bit wide, with a maximum value of one. In
    > fact, BIT is the only unsigned numeric type in SQL Server -- all
    > others are signed.
    >



    Thanks folks.

  6. Re: True and False in T-SQL

    >> I've got two variables defined as bit, @Exist_Recent and @Exist_Old. I want to use them as booleans. I'm populating them with the record [sic] counts from two different queries. <<

    You have never learned RDBMS at all, There are no Booleans in SQL;
    rows are not anything like records; fields are not anything like
    columns.

    SQL is not written with flag driven code like you posted

    Your whole approach to SQL is **fundamentally** wrong. Stop
    programming before you kill someone and at least read one book on the
    subject.


  7. Re: True and False in T-SQL

    On 14 Jul, 00:59, --CELKO-- wrote:
    > There are no Booleans in SQL;


    That's incorrect as I've mentioned before. ISO/IEC Standard SQL DOES
    have a boolean type which can be used for columns and variables. SQL
    Server on the other hand doesn't allow boolean types to be used for
    columns and variables.

    Both SQL Server and standard SQL have always supported a boolean type
    implicitly in expressions so it follows that the same ought to be
    permitted as an explicit type for columns and variables. The problem
    is that SQL's three-valued logic makes a nonsense of any attempt to
    define boolean operators for an explicit boolean type. It is arguable
    therefore that SQL's version of boolean is not a true boolean type in
    the usual mathematical sense, just as it could be argued that SQL's
    integer and other types are not truly what they claim to be due to
    three-value logic. However, within the constraints of three-value
    logic the standard has attempted to define a boolean type. It's wrong
    to claim that it doesn't exist at all.

    --
    David Portas, SQL Server MVP

    Whenever possible please post enough code to reproduce your problem.
    Including CREATE TABLE and INSERT statements usually helps.
    State what version of SQL Server you are using and specify the content
    of any error messages.

    SQL Server Books Online:
    http://msdn2.microsoft.com/library/m...S,SQL.90).aspx
    --


  8. Re: True and False in T-SQL

    Hi,

    > You have never learned RDBMS at all, There are no Booleans in SQL;
    > rows are not anything like records; fields are not anything like
    > columns.
    >
    > SQL is not written with flag driven code like you posted
    >
    > Your whole approach to SQL is **fundamentally** wrong. Stop
    > programming before you kill someone and at least read one book on the
    > subject.


    Pardon me?

    Reading your website you should be involved with the SQL standard documentation.
    I assume you don't have anything with SQL standard anymore, because this is from the SQL standard:



    4.5 Boolean types
    4.5.1 Introduction to Boolean types

    The data type boolean comprises the distinct truth values True and False. Unless prohibited by a NOT
    NULL

    constraint, the boolean data type also supports the truth value Unknown as the null value. This
    specification

    does not make a distinction between the null value of the boolean data type and the truth value
    Unknown that

    is the result of an SQL , , or ; they may be
    used

    interchangeably to mean exactly the same thing.

    The boolean data type is described by the boolean data type descriptor. The boolean data type
    descriptor contains:

    - The name of the boolean data type (BOOLEAN).

    4.5.2 Comparison and assignment of booleans

    All boolean values and SQL truth values are comparable and all are assignable to a site of type
    boolean. The

    value True is greater than the value False, and any comparison involving the null value or an
    Unknown truth

    value will return an Unknown result. The values True and False may be assigned to any site having a
    boolean

    data type; assignment of Unknown, or the null value, is subject to the nullability characteristic of
    the target.





    MSSQL server doesn't have a boolean type (as more database engines), but that doesn't mean "There
    are no Booleans in SQL".


    Regards,
    Arno Brinkman
    ABVisie

    -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
    General database development support:
    http://www.databasedevelopmentforum.com

    Firebird open source database (based on IB-OE) with many SQL-99 features :
    http://www.firebirdsql.org
    http://www.firebirdsql.info
    http://www.fingerbird.de/
    http://www.comunidade-firebird.org/

    Support list for Firebird and Interbase users :
    firebird-support@yahoogroups.com

    Nederlandse firebird nieuwsgroep :
    news://newsgroups.firebirdsql.info





  9. Re: True and False in T-SQL

    > Pardon me?

    Joe doesn't care much for ANSI SQL standards after SQL-92...

    --
    Tibor Karaszi, SQL Server MVP
    http://www.karaszi.com/sqlserver/default.asp
    http://sqlblog.com/blogs/tibor_karaszi


    "Arno Brinkman" wrote in message
    news:u0LJ6SgxHHA.4264@TK2MSFTNGP05.phx.gbl...
    > Hi,
    >
    >> You have never learned RDBMS at all, There are no Booleans in SQL;
    >> rows are not anything like records; fields are not anything like
    >> columns.
    >>
    >> SQL is not written with flag driven code like you posted
    >>
    >> Your whole approach to SQL is **fundamentally** wrong. Stop
    >> programming before you kill someone and at least read one book on the
    >> subject.

    >
    > Pardon me?
    >
    > Reading your website you should be involved with the SQL standard documentation.
    > I assume you don't have anything with SQL standard anymore, because this is from the SQL standard:
    >
    >
    >
    > 4.5 Boolean types
    > 4.5.1 Introduction to Boolean types
    >
    > The data type boolean comprises the distinct truth values True and False. Unless prohibited by a
    > NOT NULL
    >
    > constraint, the boolean data type also supports the truth value Unknown as the null value. This
    > specification
    >
    > does not make a distinction between the null value of the boolean data type and the truth value
    > Unknown that
    >
    > is the result of an SQL , , or ; they may
    > be used
    >
    > interchangeably to mean exactly the same thing.
    >
    > The boolean data type is described by the boolean data type descriptor. The boolean data type
    > descriptor contains:
    >
    > - The name of the boolean data type (BOOLEAN).
    >
    > 4.5.2 Comparison and assignment of booleans
    >
    > All boolean values and SQL truth values are comparable and all are assignable to a site of type
    > boolean. The
    >
    > value True is greater than the value False, and any comparison involving the null value or an
    > Unknown truth
    >
    > value will return an Unknown result. The values True and False may be assigned to any site having
    > a boolean
    >
    > data type; assignment of Unknown, or the null value, is subject to the nullability characteristic
    > of the target.
    >
    >
    >
    >
    >
    > MSSQL server doesn't have a boolean type (as more database engines), but that doesn't mean "There
    > are no Booleans in SQL".
    >
    >
    > Regards,
    > Arno Brinkman
    > ABVisie
    >
    > -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
    > General database development support:
    > http://www.databasedevelopmentforum.com
    >
    > Firebird open source database (based on IB-OE) with many SQL-99 features :
    > http://www.firebirdsql.org
    > http://www.firebirdsql.info
    > http://www.fingerbird.de/
    > http://www.comunidade-firebird.org/
    >
    > Support list for Firebird and Interbase users :
    > firebird-support@yahoogroups.com
    >
    > Nederlandse firebird nieuwsgroep :
    > news://newsgroups.firebirdsql.info
    >
    >
    >
    >



  10. Re: True and False in T-SQL

    BIT got deprecated in SQL-2003 and BOOLEAN is next. The preferred
    function: IS [NOT] [TRUE | FALSE | UNKNOWN]


+ Reply to Thread
Page 1 of 4 1 2 3 ... LastLast