Imply bit with constant 1 or 0 in SQL Server
SqlSql ServerTsqlBitSql Problem Overview
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)
Sql Solutions
Solution 1 - Sql
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 - Sql
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 - Sql
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 - Sql
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
Solution 5 - Sql
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
Solution 6 - Sql
The expression to use inside SELECT could be
CAST(IIF(FC.CourseId IS NOT NULL, 1, 0) AS BIT)
Solution 7 - Sql
Unfortunately, no. You will have to cast each value individually.
Solution 8 - Sql
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