Boolean logic with BITs and Booleans in SQL Server

Question

How can I mix boolean logic with bitwise and/or operators in SQL Server, specifically SQL Azure Database and/or SQL Server 2016?

To demonstrate what I'm trying to do, consider the following script:

DECLARE @String varchar(2000) = 'asdf'
DECLARE @Flag1 bit = 1
DECLARE @Flag2 bit = 0

DECLARE @Data Table
(
    Value bit
)

INSERT INTO @Data VALUES (@Flag1 | @Flag2)
--INSERT INTO @Data VALUES ((@Flag1 | @String LIKE '%qwerty%') & @Flag2)
SELECT * FROM @Data

The script works fine as-is but the moment you uncomment out that second INSERT, all hell breaks loose. I understand this is invalid syntax because I'm mixing boolean logic with bitwise operators. But what is the right way to get this logic in there? Without going crazy with CASE WHEN statements, is there some way to do this?

Answer 1

You may need to revise your code as follows:

DECLARE @String varchar(2000) = 'asdf'
DECLARE @Flag1 bit = 1
DECLARE @Flag2 bit = 0

DECLARE @Data Table
(
    Value bit
)

INSERT INTO @Data VALUES (@Flag1 | @Flag2)
INSERT INTO @Data VALUES ((@Flag1 | case when charindex('qwerty', @string ) > 0 then 1 else 0 end) & @Flag2)
SELECT * FROM @Data

Your original code has syntax error because LIKE is not an operator.

Another way is to use IIF as following

DECLARE @String varchar(2000) = 'asdf'
DECLARE @Flag1 bit = 1
DECLARE @Flag2 bit = 0

DECLARE @Data Table
(
    Value bit
)

INSERT INTO @Data VALUES (@Flag1 | @Flag2)
INSERT INTO @Data VALUES ((@Flag1 | iif( @String LIKE '%qwerty%', 1, 0)) & @Flag2)
SELECT * FROM @Data