is it possible to select EXISTS directly as a bit?

Question

I was wondering if it's possible to do something like this (which doesn't work):

select cast( (exists(select * from theTable where theColumn like 'theValue%') as bit)

Seems like it should be doable, but lots of things that should work in SQL don't ;) I've seen workarounds for this (SELECT 1 where... Exists...) but it seems like I should be able to just cast the result of the exists function as a bit and be done with it.

Answer 1

No it isn't possible. The bit data type is not a boolean data type. It is an integer data type that can be 0,1, or NULL.

Answer 2

SELECT CAST(COUNT(*) AS bit) FROM MyTable WHERE theColumn like 'theValue%'

When you cast to bit

• 0 -> 0
• everything else -> 1
• And NULL -> NULL of course, but you can't get NULL with COUNT(*) without a GROUP BY

bit maps directly to boolean in .net datatypes, even if it isn't really...

This looks similar but gives no row (not zero) if no matches, so it's not the same

SELECT TOP 1 CAST(NumberKeyCOlumn AS bit) FROM MyTable WHERE theColumn like 'theValue%'

Answer 3

No, you'll have to use a workaround.

If you must return a conditional bit 0/1 another way is to:

SELECT CAST(
   CASE WHEN EXISTS(SELECT * FROM theTable where theColumn like 'theValue%') THEN 1 
   ELSE 0 
   END 
AS BIT)

Answer 4

I believe exists can only be used in a where clause, so you'll have to do a workaround (or a subquery with exists as the where clause). I don't know if that counts as a workaround.

What about this:

create table table1 (col1   int null)
go
select 'no items',CONVERT(bit, (select COUNT(*) from table1) )   -- returns 'no items', 0
go
insert into table1 (col1) values (1)
go
select '1 item',CONVERT(bit, (select COUNT(*) from table1) )     --returns '1 item', 1
go
insert into table1 (col1) values (2)
go
select '2 items',CONVERT(bit, (select COUNT(*) from table1) )    --returns '2 items', 1
go
insert into table1 (col1) values (3)
go
drop table table1
go