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 shorthand way of expressing the values as bit type without having to cast every time?

(I'm using MS SQL Server 2005)

Answer 1

Use the below query:

cast (

  case

    when FC.CourseId is not null then 1 else 0

  end

as bit)

In the above code, the CAST spec is "CAST (expression AS type)". And, the CASE is an expression in the above context.

If you have multiple such expressions, you can declare bit vars @true and @false and use them. Or, you can use UDFs.

DECLARE @True bit, @False bit;

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

SELECT 

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