We often write code that uses switches. Usually switches are individual variables that represent ON and OFF conditions. We almost always check switches one at a time with simple IF or CASE statements. This is fine as long as the number of switches is small.

If the number of switches is large, or grows large over time, our IF and CASE statements can become very large and very complex. Sometimes they become almost impossible to understand. Also the switches are still usually checked one at a time even when there are large numbers of them.

TestBits is a function that will quickly handle large numbers of switches. It can check a combination of many switches at once, without complex statements, and with very little code. This function will help simplify your code and make it more maintainable.

The secret is to use a small number of bitwise operations to set and test individual bits in a smallint variable. The individual bits in the smallint will be the switches. This article will discuss using 15 of the 16 bits in a smallint. You can test any one of 32,768 combinations among them at the same time. If you need more switches you can use an int instead of a smallint. An int can support 31 switches and 2,147,483,648 combinations.

Note - The highest bit (the leftmost bit) in a smallint or int is the sign bit. In this discussion it will always be set to 0 (positive) and it will not be used as a switch.

Refresher, a little "bit" of Boolean

First, we need to refresh ourselves a little about Boolean algebra and a little about T-SQL bitwise operators. Just a little.

If you are comfortable with Boolean algebra and bitwise operators, just read the "Useful takeaway" notes in each section and skip the rest.

You will need to convert between binary and decimal numbers.  If you don't know how to do this by hand just use a calculator.

Binary representation of data

A smallint has 16 bit positions, but this discussion will only use the right-hand four bits. These are the "lowest" bits. All the higher bits will be set to zero. 

The value of the bits in the positions from right to left, are 1, 2, 4, 8  and so on. The values increase by a power of 2 in each position from right to left. If a bit is 1, its value is added to the value of the variable.

bit position:                8 4 2 1

bit values:   0000 0000 0000 1 1 0 0 = 12 (decimal)

In the example above, the value of the number is 8 + 4 + 0 + 0 = 12

This article will use a shorter representation of binary numbers. The higher (more left) bits won't be shown because they will all be 0.

bit position  8 4 2 1

@switches     1 1 0 0 = 12

OR (a | b)

When one variable is ORed with another the result will have all bits set to 1 that were 1 in either of the two variables.

Example:  SET @result = @switches | @mask

bit position  8 4 2 1

@switches     1 1 0 0 = 12

 | @mask      0 1 1 0 = 6

 = @result    1 1 1 0 = 14

Useful takeaway:

To set a bit to 1 (ON), OR it with a 1 (as in the "2" position above).

Example: set the @houseCombo variable to zero, then turn on the bits in the 2 and 4 positions.

SET @stopMail = 2

SET @lockDoors = 4

SET @comboHouse = 0 | @stopMail | @lockDoors

In the example, above, this line of code would also work

SET @comboHouse = 6

but it's better to create named variables for each of the bits you want to use.  This will make the code more legible and much easier to maintain.

AND (a & b)

When one variable is ANDed with another the result will have all bits set to 1 that were 1 simultaneously in the two variables.

Example:  SET @result = @switches & @mask

bit position  8 4 2 1

@switches     1 1 0 0 = 12

 & @mask      0 1 1 0 = 6

 = @result    0 1 0 0 = 4

Useful takeaway:

To set a bit to 0 (OFF), AND it with a 0 (as in the "8" position above).

EXCLUSIVE OR (a ^ b)

When one variable is Exclusive ORed with another the result will have all bits set to 1 that were 1 in either of the two variables, but not 1 in both of them simultaneously.

Example 1:  SET @result = @work ^ @mask

bit position  8 4 2 1

@work         1 1 0 0 = 12

 ^ @mask      0 1 1 0 = 6

 = @result    1 0 1 0 = 10

Example 2:  Same as above, but @work=@mask

bit position  8 4 2 1

@work         1 1 0 0 = 12

 ^ @mask      1 1 0 0 = 12

 = @result    0 0 0 0 = 0 (zero, @work=@mask)

Useful takeaway:

You can logically test to see whether two variables are the same by using Exclusive OR.  This removes the need for special processing of negative numbers.  When all zeroes is the result of one variable being Exclusive ORed with another, the variables are equal.

When using signed integers for switches, I prefer to check for equality with Exclusive OR instead of algebraic Equal to avoid special processing of the sign.

The TestBits Function

This function will enable you to test combinations of selected bits in a smallint.  TestBits will check the entire combination of selected bits all at once rather than one bit at a time. You may need to do this in applications to evaluate different combinations of conditions, such as:

• Combinations of checklist items.
• Combinations of education and experience on a resume.
• Combinations of items in an account history that enable other bank services.
• Combinations of conditions to determine routing of a document.

Example

TestBits takes two parameters. The first is the variable containing the switches. The second is a “mask” in which you tell TestBits which switches you are interested in by setting the corresponding mask bits to 1. That is, if you want to test for the 2-bit and the 4-bit you set the mask to 0110 (6 decimal).

TestBits returns a bit variable 0, 1, or NULL, which will be explained a little later.

    @Result = dbo.fn_TestBits(@switches, @mask)

In the example, below, the bits in the @switches variable represent the items on a checklist of things to do before a camping trip.  Switches have been set to 1 as various tasks have been completed.  Some tasks may not have been completed yet.

The switches in the 1 through 8 positions represent these tasks:

  1 - Check the tire pressures of the bicycles

  2 - Stop the mail service at the house

  4 - Lock the doors of the house

  8 - Leave contact information with the neighbors

The power of combinations

We can check combinations of several switches with one call to TestBits.  For example:

• We can check for stopping the mail and locking the doors with one call. In that case we would be looking for the 2-bit and the 4-bit to both be 1. The other bits wouldn't matter one way or the other. We will call this combination "comboHouse".
• We can test for all of the bits to be 1, indicating that the checklist is complete. We will call this combination "comboAll".

It is in combinations like these that TestBits delivers the most power.  In a real-world example there would be many switches and many combinations of them.

-- All of the variables have been
-- declared as smallint except @Result,
-- which is a bit variable.
-- The bits in @switches have been set
-- as items have been completed.  Some may
-- not yet be complete.
-- Set named variables with values
-- of various bit positions
SET @checkTires = 1
SET @stopMail = 2
SET @lockDoors = 4
SET @tellNeighbors = 8
SET @comboHouse
  = 0 | @stopMail | @lockDoors  -- 6
SET @comboAll
  = 0 | @checkTires | @stopMail
      | @lockDoors | @tellNeighbors  -- 15
SELECT
  dbo.fn_TestBits(@switches, @comboHouse)
    AS HouseItems,
  dbo.fn_TestBits(@switches, @comboAll)
    AS AllItems

Simplicity and legibility

You can see in this example that it would be very easy to add other switches and combinations. There is not a complex IF or CASE statement in sight. This kind of code is very easy to read and to maintain.

How the TestBits function works

1.  AND the mask with the switches and store the result in a variable named @work.  This has the effect of setting 0 in all the bits in @work that were 0 in @mask.  This is so that @work won't have switches set to 1 that the user did not select in the @mask.

   SET @work = @switches & @mask

Example 1 - @mask not zero,

            no bits match

  @switches  1 1 0 0

   & @mask   0 0 1 1

   = @work   0 0 0 0  (zero)

Example 2 - @mask is zero

  @switches  1 1 0 0

   & @mask   0 0 0 0  (mask zero)

   = @work   0 0 0 0  (zero)

Example 3 - @mask not zero,

            some or all bits match

  @switches  1 1 0 0

   & @mask   0 1 1 1

   = @work   0 1 0 0  (not zero)

These examples show that the result variable @work will be all zeroes if all of the bits that are 1 in the mask correspond to switches that are 0 (Example 1).  @work will also be all zeroes if all of the bits in the mask itself were 0 in the first place (Example 2).

In the Example 1 and 2 cases we will set the TestBits returned result to 0.

 SET @Result =
    CASE
      -- all selected switches are 0
      -- or @mask = is all 0
      WHEN @work = 0 THEN 0

2.  If @work is not all zeroes, make a second check to see whether @work is equal to @mask. If it is equal to @mask, all of the bits that are 1 in the mask correspond to switches that are set to 1. TestBits checks for this equality with an Exclusive OR:

    @switches  1 1 0 0

     & @mask   0 1 0 0  (AND)

     = @work   0 1 0 0  (not zero)

    @work      0 1 0 0

     ^ @mask   0 1 0 0  (Exclusive OR)

               0 0 0 0  (zero so

                          @work=@mask)

If this is true we will set the TestBits returned result to 1.

      -- all selected switches are 1
      WHEN (@work ^ @mask) = 0 THEN 1

3.  We get to this third case only when neither of the WHEN conditions above is true. That is, all the selected switches are not set to 0, and all of them are not set to 1. If they are not all 0 and not all 1, then they must be a mixture of some that are 0 and some that are 1.

In this case we will set the TestBits returned result to NULL:

      ELSE
      -- then the selected switches
      -- must be a mix of 0 and 1
      NULL
    END  

Conclusion

Your application may have a need to use many binary switches and to test for combinations of them.  If this is true, the use of bitwise operators and the TestBits function can simplify code that would otherwise need to contain complex IF or CASE statements.  TestBits is especially useful when there are combinations of switches that need to be tested.