In TSQL, how to evaluate an expression and assign it to a BIT field?

Question

I'm struggling on something very simple. I'm trying to assign the results of a boolean expression to a BIT variable.

Basically I would like to do something like:

DECLARE @is_search_term_empty BIT

SET @is_search_term_empty = (@search_term = '')

where @search_term is a NVARCHAR(128) declared somewhere else in the code.

I cannot work out the syntax to evaluate something and assign it to a BIT variable, ie:

SET @is_search_term_empty = (1 > 2) 

Thanks.

Solution 1

You can do this with a CASE statement:

DECLARE @bitvar BIT 
DECLARE @search_term varchar(128)

set @search_term = 'abc'

SET @bitvar = CASE 
                 WHEN (@search_term = 'abc') THEN 1
                 ELSE 0
              END

select @bitvar

Solution 2

From SQL 2012 you can now use the IIf() function. Your example would now look like;

DECLARE @is_search_term_empty BIT = IIf(@search_term = '', 1, 0);

This is still essentially a CASE statement under the hood but is easier to digest.

Solution 3

You can simply set a default value for your bit variable then just apply a IF statement on it like this:

DECLARE @bitvar BIT = 0                    -- << Default value
DECLARE @search_term varchar(128)

set @search_term = 'abc'
IF (@search_term = 'abc') SET @bitvar = 1  -- << Value changes if required

select @bitvar

Solution 4

One of the reasons this is so hard to do is that T-SQL uses three-valued boolean logic. TRUE and FALSE are not the only options; there's also UNKNOWN. We have a similar concept with NULL values, and lots of language features exist to translate from values to boolean expressions (=, IS NULL, IS NOT NULL, etc.), but there's no such thing as IS UNKNOWN, or other language features to translate directly from a boolean expression to a bit datatype.

Here's an example solution. Unfortunately this requires that the expression you are testing (1 = NULL, here) needs to be duplicated in code -- you could avoid this with sp_executesql and output parameters if you really needed to:

DECLARE @bit1 bit = 0,
        @bit2 bit = 0,
        @result bit;

IF (1 = NULL) SET @bit1 = 1;
IF NOT(1 = NULL) SET @bit2 = 1;

IF @bit1 = @bit2 SET @result = NULL;
ELSE IF @bit1 = 1 SET @result = 1;
ELSE IF @bit2 = 1 SET @result = 0;

SELECT @result as [bit];

This example relies on the fact that NOT(UNKNOWN) returns UNKNOWN, not TRUE. This obviously relies on your BIT column being nullable. Similarly, if you wish to avoid three-valued boolean logic, you must ensure that none of your expression inputs can ever be null.

View more solutions

Comments

[Giuseppe Romagnuolo]

I thought for a second of using a CASE statement but I discarded soon believing that was a nasty hack! :P (Is it me or SQL syntax sometimes is so weird) Anyway, thanks for help! :-)

[Mitch Wheat]

Would the downvoter to this old, accepted answer please leave a comment. Thanks.