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)

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.

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

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

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

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)

Tested and functional

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