When 2 = 1 in data type bit

So interesting issue, of tracking down why a filter stored procedure was not working. I first went through and captured a trace with SQL Server Profiler and extracted the call being made to the database. So pasting this into the query window and then using the Debug feature in SSMS 2008 to step into the stored procedure (sp).

In stepping through the sp I discovered the variable value was transformed into 1 in my where clause. Wow, I know it was 2, back to checking why this was happening. I checked the type and it was a BIT, not an INT. My user interface was sending a value of 2.  However, the behavior was not to give a value out of range, or implicit conversion error, it was to convert it to 1. I was under the impression the only valid values were NULL, 0, or 1. Be careful anticipating the behavior of languages. Let's illustrate this behavior take the following code sample and results:



declare @SearchValue bit
select @SearchValue N'as no value'
set @SearchValue = 0 
select @SearchValue N'as 0'
set @SearchValue = 1
select @SearchValue N'as 1'
set @SearchValue = 2
select @SearchValue N'as 2'