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 short hand way of expressing the values as bit type without having to cast every time    I m using MS SQL Server 2005

User · Answer

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

User · Answer

Unfortunately  no   You will have to cast each value individually

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

The expression to use inside SELECT could be  CAST IIF FC CourseId IS NOT NULL  1  0  AS BIT

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

Examples related to sql

Examples related to sql-server

Examples related to tsql

Examples related to bit