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)

Answer 1

Enjoy this :) Without cast each value individually.

SELECT ...,
  IsCoursedBased = CAST(
      CASE WHEN fc.CourseId is not null THEN 1 ELSE 0 END
    AS BIT
  )
FROM fc

Answer 2

The expression to use inside SELECT could be

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

Answer 3

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

Answer 4

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.

Answer 5

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

Answer 6

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 ...