Imply bit with constant 1 or 0 in SQL Server

Solution 1:

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

Solution 2:

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

Solution 3:

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.

Solution 4:

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