[sql] Imply bit with constant 1 or 0 in SQL Server

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)

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

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.

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

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

The expression to use inside SELECT could be

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

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

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