Imply bit with constant 1 or 0 in SQL Server

Question

Is it possible to express 1 or 0 as a bit when used as a field value in a select statement?

e.g.

In this case statement (which is part of a select statement) ICourseBased is of type int.

case 
when FC.CourseId is not null then 1
else 0
end
as IsCoursedBased

To get it to be a bit type I have to cast both values.

case 
when FC.CourseId is not null then cast(1 as bit)
else cast(0 as bit)
end
as IsCoursedBased

Is there a short hand way of expressing the values as bit type without having to cast every time?

(I'm using MS SQL Server 2005)

gbn · Accepted Answer · 2011-08-09T10:53:14.350

255

cast (
  case
    when FC.CourseId is not null then 1 else 0
  end
as bit)

The CAST spec is "CAST (expression AS type)". The CASE is an expression in this context.

If you have multiple such expressions, I'd declare bit vars @true and @false and use them. Or use UDFs if you really wanted...

DECLARE @True bit, @False bit;
SELECT @True = 1, @False = 0;  --can be combined with declare in SQL 2008

SELECT
    case when FC.CourseId is not null then @True ELSE @False END AS ...

edited Aug 09 '11 at 10:53

answered Jul 23 '09 at 16:04

gbn

422,506
82
585
676

1

To make the code more readable we standardised our SQL and now use declared bit variables when we need to express true/false. – Damien McGivern Feb 22 '11 at 15:54
@Damien McGivern: I found it useful too when I had many CASTs – gbn Feb 22 '11 at 15:58
Are there any performance-implications of using declared variables? What if you want to use this in a `VIEW` or single-statement table-valued `UDF`? – Dai Sep 03 '20 at 02:17
@Dai trivial. I did say "multiple such expressions" which is usually a stored procedure – gbn Sep 17 '20 at 12:45

score 11 · Answer 2 · answered Jul 23 '09 at 16:07

11

You might add the second snippet as a field definition for ICourseBased in a view.

DECLARE VIEW MyView
AS
  SELECT
  case 
  when FC.CourseId is not null then cast(1 as bit)
  else cast(0 as bit)
  end
  as IsCoursedBased
  ...

SELECT ICourseBased FROM MyView

answered Jul 23 '09 at 16:07

Michael Petrotta

59,888
27
145
179

1

This works, but has the downside that cast() returns a NULL type. – Dan Jan 20 '15 at 11:35
1

@Dan There's always `ISNULL( CAST(1 AS bit), CAST(0 AS bit) )` (yes, I'm very frustrated by how verbose SQL is...) – Dai Sep 03 '20 at 02:18

score 7 · Answer 3 · edited Jan 23 '14 at 18:15

7

Slightly more condensed than gbn's:

Assuming CourseId is non-zero

CAST (COALESCE(FC.CourseId, 0) AS Bit)

COALESCE is like an ISNULL(), but returns the first non-Null.

A Non-Zero CourseId will get type-cast to a 1, while a null CourseId will cause COALESCE to return the next value, 0

edited Jan 23 '14 at 18:15

ckpepper02

3,297
5
29
43

answered Jan 23 '14 at 17:49

kpkpkp

135
1
7

I like the slickness of this solution... but the original post said nothing about FC.CourseId being non-zero. – Joe Sep 14 '20 at 18:32

score 6 · Answer 4 · answered Jul 23 '09 at 16:04

6

No, but you could cast the whole expression rather than the sub-components of that expression. Actually, that probably makes it less readable in this case.

answered Jul 23 '09 at 16:04

Gary McGill

26,400
25
118
202

score 4 · Answer 5 · answered Sep 14 '15 at 11:35

4

If you want the column is BIT and NOT NULL, you should put ISNULL before the CAST.

ISNULL(
   CAST (
      CASE
         WHEN FC.CourseId IS NOT NULL THEN 1 ELSE 0
      END
    AS BIT)
,0) AS IsCoursedBased

answered Sep 14 '15 at 11:35

Fábio Nascimento

2,644
1
21
27

score 1 · Answer 6 · edited Oct 30 '18 at 11:07

1

The expression to use inside SELECT could be

CAST(IIF(FC.CourseId IS NOT NULL, 1, 0) AS BIT)

edited Oct 30 '18 at 11:07

ttarchala

4,277
2
26
36

answered Jan 25 '17 at 22:29

IamProfChaos

29
1

1

Make this answer more useful and explain or provide links to library/docs – happymacarts Jan 25 '17 at 22:40
Hello, and welcome to SO. Please explain your answer. – Chait Jan 25 '17 at 23:15
Although [IIF](https://learn.microsoft.com/en-us/sql/t-sql/functions/logical-functions-iif-transact-sql?view=sql-server-2017) looks better than `case when ... else ... end`, one still has to cast `1` and `0` to the BIT type. – Fabio A. Jul 03 '18 at 22:39

score 0 · Answer 7 · answered Jul 23 '09 at 16:04

0

Unfortunately, no. You will have to cast each value individually.

answered Jul 23 '09 at 16:04

Andrew Hare

344,730
71
640
635

Adrian S. · Answer 8 · 2023-02-17T08:45:23.410

-2

Tested and functional

SELECT fc.CourseId, IsCoursedBased = CAST(CASE WHEN fc.CourseId IS NOT NULL THEN 1 ELSE 0 END AS BIT) 
FROM fc

edited Feb 17 '23 at 08:45

answered Dec 13 '17 at 14:10

Adrian S.

129
1
2
12

Imply bit with constant 1 or 0 in SQL Server

8 Answers8

Linked