SQL -- Assigning a bit variable in select statement

Question

For example:

declare @bitHaveRows bit
 select @bitHaveRows = count(*)
   from table
  where (predicate)

Are there any functions I can call on this line:

select @bitHaveRows = count(*)

to assign this bit a value of 0 if there are no rows, or 1 if there are one or more rows?

Joel Coehoorn · Accepted Answer · 2024-04-05 13:48:04Z

7

According to the conversion chart, there's an implicit conversion from int to bit. But if for some reason that doesn't work:

CASE WHEN COUNT(*) > 0 THEN 1 ELSE 0 END

answered May 18, 2009 at 21:12

Joel Coehoorn

418k114 gold badges582 silver badges820 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

That's what I needed. Thanks. I was hoping the implicit conversion would work in that context but apparently not.

Dan · Accepted Answer · 2010-07-23 15:41:58Z

7

declare @bRowsExist
SELECT @bRowsExist = CAST(count(*) as bit)
FROM yourtable

...not sure if it's a better query than the other suggestions

answered Jul 23, 2010 at 15:41

Dan

711 silver badge2 bronze badges

Andomar · Accepted Answer · 2009-05-18 21:12:52Z

1

If your database supports it, you can use a CASE statement:

declare @bitHaveRows
select @bitHaveRows = case when count(*) > 0 then 1 else 0 end
from yourtable

answered May 18, 2009 at 21:12

Andomar

239k55 gold badges387 silver badges412 bronze badges