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.
This question is related to
sql
sql-server
boolean
literals
You can use 'True' or 'False' strings for simulate bolean type data.
Select *
From <table>
Where <columna> = 'True'
I think this way maybe slow than just put 1 because it's resolved with Convert_implicit function.
select * from SomeTable where null is null
or
select * from SomeTable where null is not null
maybe this is the best performance?
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.
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)
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<>0" ... and this works. Good luck!
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.
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'
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
.
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"
.
I question the value of using a Boolean in TSQL. Every time I've started wishing for Booleans & For loops I realised I was approaching the problem like a C programmer & 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 & Temp Tables you have in your TSQL, the more likely you should refactor it.
select * from SomeTable where 1=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.
Source: Stackoverflow.com