Imply Bit with Constant 1 or 0 in SQL Server

Imply bit with constant 1 or 0 in SQL Server

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

Cast field to bit 0 if null and bit 1 if not

You can use exists and case expression:

select personID
     , name
     , case when exists ( select *
                          from parents
                          where parents.personID=persons.personID) then 1 else 0 end as inUse
from persons

Or just perform a left join:

select distinct pe.personID
     , pe.name
     , case when pa.personID is null then 0 else 1 end as inUse
from persons pe
left join parents pa on pa.personID = pe.personID

Return BIT constant from SQL Stored Procedure

Some people prefer to do the following

declare @True bit,@False bit
set @True = 1
set @False = 0

And then use @True or @False for the remainder of their query.

I would imagine that post compilation the difference in efficiency between the two methods is so vanishingly small you can safely write it in whatever style you prefer.

SQL Server if bit parameter = 0 return records where field has value of 0 or null

Ok, this solution looks like it is a lot of typing but if you have a function on the predicate column in the WHERE clause, sql server most likely going to scan the whole table and not going to use your indexes:

SELECT * FROM Carriers c
WHERE @Intrastate IS NULL
      OR (@Intrastate = 1 AND c.Intrastate = 1)
      OR ( @Intrastate = 0 AND (  c.Intrastate IS NULL 
                                  OR c.Intrastate = 0 ))

Return zero if value less than zero

Just for fun:

DECLARE @a INT = -3
SELECT COALESCE(NULLIF (ABS(@a), -@a), 0)

This post just hints in the direction that a CASE expression is a much better option to get the expected result.

NULLIF is, after all, just a fancy CASE. The example query above expands (in the execution plan) to:

CASE 
    WHEN 
        CASE 
            WHEN abs([@a])=( -[@a]) 
            THEN NULL 
            ELSE abs([@a]) 
        END IS NOT NULL 
    THEN 
        CASE 
            WHEN abs([@a])=( -[@a]) 
            THEN NULL 
            ELSE abs([@a]) 
        END 
    ELSE (0) 
END

A suitable CASE expression:

-- All versions
SELECT CASE WHEN @a > 0 THEN @a ELSE 0 END;

-- SQL Server 2012 or later
SELECT IIF(@a > 0, @a, 0);

SQL Server - Bit data type matches String 'True' and 'False'

Here's the excerpt from the bit data type documentation that describes this behavior:

The string values TRUE and FALSE can be converted to bit values: TRUE
is converted to 1 and FALSE is converted to 0.

Set Undefined to BIT values

ISNULL(check_expression, replacement_value) returns the same type as check_expression which in your case is BIT and this is why you get 1 instead of -1.


Instead, if you want the resut as an integer in the SELECT statement, use COALESCE(expression \[ ,...n \]) which returns the data type of expression with the highest data type precedence and since BIT has lower precedence than any integer data type, you will get -1 as result:

SELECT COALESCE(EC.isWantedBack,-1) AS isWantedBack

You could also achieve the same with a CASE expression:

SELECT CASE WHEN EC.isWantedBack IS NULL THEN -1 ELSE isWantedBack END AS isWantedBack

See a simplified demo.

Applying the MIN aggregate function to a BIT field

Since there are only two options for BIT, just use a case statement:

SELECT CASE WHEN EXISTS (SELECT 1 FROM ....) THEN 1 ELSE 0 END AS 'MinBit'
FROM ...
WHERE ...

This has the advantage of:

• Not forcing a table scan (indexes on BIT fields pretty much never get used)
• Short circuiting TWICE (once for EXISTS and again for the CASE)

It is a little more code to write but it shouldn't be terrible. If you have multiple values to check you could always encapsulate your larger result set (with all the JOIN and FILTER criteria) in a CTE at the beginning of the query, then reference that in the CASE statements.

---

Related Topics