SQL Server - boolean literal?

Question

How to write literal boolean value in SQL Server? See sample use:

select * from SomeTable where PSEUDO_TRUE

another sample:

if PSEUDO_TRUE
begin
  select 'Hello, SQL!'
end 

Note: The query above has nothing to do with how I'm going to use it. It is just to test the literal boolean.

Answer 1

You should consider that a "true value" is everything except 0 and not only 1. So instead of 1=1 you should write 1<>0.

Because when you will use parameter (@param <> 0) you could have some conversion issue.

The most know is Access which translate True value on control as -1 instead of 1.

Answer 2

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".

Answer 3

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.

Answer 4

According to Microsoft: syntax for searching is

[ WHERE <search_condition> ]*

And search condition is:

<search_condition> ::= 
    { [ NOT ] <predicate> | ( <search_condition> ) } 
    [ { AND | OR } [ NOT ] { <predicate> | ( <search_condition> ) } ] 
[ ,...n ] 

And predicate is:

<predicate> ::= 
    { expression { = | < > | ! = | > | > = | ! > | < | < = | ! < } 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' 

Answer 5

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.

Answer 6

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)