## BIT Data Type

By , February 23, 2011 20:18

I stumbled upon (or rather over) a little something this morning while I was writing a procedure one of whose parameters was of type bit.

Books Online states that the bit datatype is “An integer data type that can take a value of 1, 0, or NULL”.

Anyway, I wrote the procedure and while testing it, I was expecting to get an error when passing anything other than 1, 0 or NULL the the bit parameter. I was surprised to find that the bit will always have the value 1 when a value other than 0 or NULL is passed to it, even if the value is not an integer, but rather of type numeric (i.e. decimal or float). Even negative values are interpreted as 1.

 ```SET NOCOUNT ON DECLARE @nullbit BIT DECLARE @falsebit BIT DECLARE @truebit BIT DECLARE @posbit BIT DECLARE @negbit BIT DECLARE @decimalbit BIT SET @falsebit = 0 SET @truebit = 1 SET @posbit = CAST(POWER(2.0,63 -1) AS BIGINT) SET @negbit = CAST(POWER(-2.0,63) AS BIGINT) SET @decimalbit = -0.1 SELECT @nullbit AS [NULL], @truebit AS [TRUE], @falsebit AS [FALSE], @posbit AS positive, @negbit AS negative, @decimalbit AS [DECIMAL] SET NOCOUNT OFF```

Discovering that even bigint values were permitted, I decided to push it a little. Try the following and you’ll see that all values, between -2^122 and 2^122 are interpreted as 1 (with, of course, 0 as an exception).

 ```SET NOCOUNT ON DECLARE @results TABLE (expression FLOAT(53), bitvalue BIT) DECLARE @i FLOAT(53) = -122.0 WHILE 1=1 BEGIN BEGIN TRY INSERT INTO @results SELECT @i, CASE WHEN @i < 0 THEN CAST(POWER(-2.0,@i * -1) AS BIT) WHEN @i = 0 THEN CAST(@i AS BIT) ELSE CAST(POWER(2.0,@i) AS BIT) END SET @i += 1 END TRY BEGIN CATCH SELECT * FROM @results RETURN END CATCH END SET NOCOUNT OFF```

Conclusion
I was expecting my procedure to report some sort of error when a "false" value was passed to it. I think the thing to learn here is: always test to see if things work the way you expect them to.