Comments (0)

By Sean, 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.