CODEWITHSUNDEEP
sql-serverstored-proceduresbit
Csharp
Csharp

Reputation: 2982

returning a bit type in a stored procedure

I have a stored procedure in which I am validating some columns to return a value of 1 or 0.

The stored proc code block is: 

, CASE WHEN 
    (PolicyStartMileage < LossMileage) 
    AND (PolicyStartDate <= LossDate) 
    AND (Certified = 0) 
     THEN '1' 
     ELSE '0' 
     END
     AS Certifiable

My question is, how do I return '1' or '0' as a bit type?

In my codebehind, I am using: 

Boolean.Parse(item.GetDataKeyValue("Certifiable").ToString());

in order to read the value (true or false).

Upvotes: 1

Views: 2032

Answers (3)

Austin
Austin

Reputation: 904

If you want the data to be a true BIT data type you need to do a cast, otherwise it will default to numeric.

CASE WHEN 
    (PolicyStartMileage < LossMileage) 
    AND (PolicyStartDate <= LossDate) 
    AND (Certified = 0) 
     THEN CAST(1 AS BIT) 
     ELSE CAST(0 AS BIT) 
     END
     AS Certifiable

If you do the cast, you can call the GetBoolean() method from the data reader directly avoiding the parse

Upvotes: 4

Rahul Tripathi
Rahul Tripathi

Reputation: 172468

Try to remove the quotes '' around 1 and 0 so that it behaves as integers rather than string. like this:

, CASE WHEN 
(PolicyStartMileage < LossMileage) 
AND (PolicyStartDate <= LossDate) 
AND (Certified = 0) 
 THEN 1 
 ELSE 0 
 END
 AS Certifiable

Upvotes: 1

M.Ali
M.Ali

Reputation: 69524

, CASE WHEN 
    (PolicyStartMileage < LossMileage) 
    AND (PolicyStartDate <= LossDate) 
    AND (Certified = 0) 
     THEN 1 
     ELSE 0 
     END
     AS Certifiable

when you use Quotes around 1 or 0 it treats it as a string not integer.

Upvotes: 1

Related Questions