SQL Server - boolean literal

Question

How to write literal boolean value in SQL Server?
select * from SomeTable where PSEUDO_TRUE

There is no such thing.

You have to compare the value with something using = < > like .... The closest you get a boolean value in SQL Server is the bit. And that is an integer that can have the values null, 0 and 1.

User · Answer

How to write literal boolean value in SQL Server?
select * from SomeTable where PSEUDO_TRUE

There is no such thing.

You have to compare the value with something using = < > like .... The closest you get a boolean value in SQL Server is the bit. And that is an integer that can have the values null, 0 and 1.

User · Answer

I question the value of using a Boolean in TSQL   Every time I ve started wishing for Booleans  amp  For loops I realised I was approaching the problem like a C programmer  amp  not a SQL programmer  The problem became trivial when I switched gears   In SQL you are manipulating SETs of data   WHERE BOOLEAN  is ineffective  as does not change the set you are working with  You need to compare each row with something for the filter clause to be effective  The Table Resultset is an iEnumerable  the SELECT statement is a FOREACH loop   Yes   WHERE IsAdmin   True  is nicer to read than  WHERE IsAdmin   1   Yes   WHERE True  would be nicer than  WHERE 1 1       when dynamically generating TSQL   and maybe  passing a Boolean to a stored proc may make an if statement more readable    But mostly  the more IF s  WHILE s  amp  Temp Tables you have in your TSQL  the more likely you should refactor it

User · Answer

SQL Server does not have literal true or false values  You ll need to use the 1 1 method  or similar  in the rare cases this is needed   One option is to create your own named variables for true and false  DECLARE  TRUE bit DECLARE  FALSE bit SET  TRUE   1 SET  FALSE   0  select   from SomeTable where  TRUE    TRUE   But these will only exist within the scope of the batch  you ll have to redeclare them in every batch in which you want to use them

User · Answer

Most databases will accept this   select   from SomeTable where true   However some databases  eg SQL Server  Oracle  do not have a boolean type  In these cases you may use   select   from SomeTable where 1 1   BTW  if building up an sql where clause by hand  this is the basis for simplifying your code because you can avoid having to know if the condition you re about to add to a where clause is the first one  which should be preceded by  WHERE    or a subsequent one  which should be preceded by  AND    By always starting with  WHERE 1 1   all conditions  if any  added to the where clause are preceded by  AND

User · Answer

SQL Server doesn t have a boolean data type  As  Mikael has indicated  the closest approximation is the bit  But that is a numeric type  not a boolean type  In addition  it only supports 2 values - 0 or 1  and one non-value  NULL    SQL  standard SQL  as well as T-SQL dialect  describes a Three valued logic  The boolean type for SQL should support 3 values - TRUE  FALSE and UNKNOWN  and also  the non-value NULL   So bit isn t actually a good match here   Given that SQL Server has no support for the data type  we should not expect to be able to write literals of that  type

User · Answer

select   from SomeTable where 1 1

User · Answer

According to Microsoft  syntax for searching is    WHERE  lt search condition gt       And search condition is    lt search condition gt               NOT    lt predicate gt       lt search condition gt               AND   OR     NOT      lt predicate gt       lt search condition gt               n      And predicate is    lt predicate gt             expression        lt   gt           gt     gt         gt     lt     lt         lt    expression    As you can see  you always have to write two expressions to compare  Here search condition is  boolean expression like 1 1  a  b  Do not confuse search expressions with boolean constants like  True  or  False   You can assign boolean constants to BIT variables   DECLARE  B BIT SET  B  True    but in TSQL you can not use boolean constants instead of boolean expressions like this   SELECT   FROM Somewhere WHERE  True    It will not work   But you can use boolean constants to build two-sided search expression like this   SEARCH   FROM Somewhere WHERE  True   True

User · Answer

select   from SomeTable where null is null   or  select   from SomeTable where null is not null   maybe this is the best performance

User · Answer

You should consider that a  true value  is everything except 0 and not only 1  So instead of 1 1 you should write 1 lt  0   Because when you will use parameter   param  lt   0  you could have some conversion issue   The most know is Access which translate True value on control as -1 instead of 1

User · Answer

You can use the values  TRUE  and  FALSE   From https   docs microsoft com en-us sql t-sql data-types bit-transact-sql      The string values TRUE and FALSE can be converted to bit values  TRUE   is converted to 1 and FALSE is converted to 0

User · Answer

This isn t mentioned in any of the other answers  If you want a value that orms  should  hydrate as boolean you can use  CONVERT bit  0  -- false CONVERT bit  1  -- true  This gives you a bit which is not a boolean  You cannot use that value in an if statement for example   IF CONVERT bit  0  BEGIN     print  Yay  END   woudl not parse  You would still need to write  IF CONVERT bit  0    0   So its not terribly useful

User · Answer

I hope this answers the intent of the question   Although there are no Booleans in SQL Server  if you have a database that had Boolean types that was translated from Access  the phrase which works in Access was     WHERE Foo   Foo is the Boolean column name    It can be replaced by     WHERE Foo lt  0      and this works   Good luck

User · Answer

You can use  True  or  False  strings for simulate bolean type data   Select   From  lt table gt  Where  lt columna gt     True    I think this way maybe slow than just put 1 because it s resolved with Convert implicit function

[sql] SQL Server - boolean literal?

Examples related to sql

Examples related to sql-server

Examples related to boolean

Examples related to literals