RSS Source:  http://www.damba.net

Flexible T-SQL:  Bending T-SQL to Get Around Everyday Programming Obstacles

My last blog post has evolved into a presentation on T-SQL that will be given in June at the Macon .NET User’s group.  The presentation will cover three parts: Using OpenXML to pass arrays to stored procedures, using Partion Over and Row_Number to narrow down queries, and using Bitwise Operators to generator more flexible queries.   This blog post continues the Bitwise Operator entry.

First, a quick note about Bitwise – it must be an integer.  That presents a limit of 32 different bitwise categories for SQL Server 2005.  That’s because the integer on SQL Server is 4 bytes, which yields 2^31.

We want to make an easy way to directly connect a Car table with the Bitwise Options table.  As noted in the first part of this post, Bitwise operators require specifying the categories: e.g. to find Option 8, the query must be:

Select CarId from Car where OptionId & 8 = 8. 

That’s fine, except this step must be repeated for each Option:

                Select CarId from Car where OptionId & 8 = 8 or OptionId & 16 = 16.

This itemization will get tedious after awhile, especially if the need is felt to create suboptions to extend the 32 option limitation.  Fortunately, there is a way around this by using a WHILE loop in T-SQL.

--This table stores the relevant bitwiseids

Declare @BitTable as Table

(

      BitCodeId int

)

--This section loops through the bitwiseids and places them in the stored procedure

DECLARE @bitMask INT

DECLARE @counter INT

BEGIN

      SET @bitMask = 1

      SET @counter = 1

      WHILE (@counter < 31)

      BEGIN

            IF ((@OptionId & @bitMask) = @bitMask)

            BEGIN

                  Insert Into @BitTable (BitCodeId) Values (@BitMask)              

            END

            SET @bitMask = @bitMask * 2

            SET @counter = @counter + 1

      END

      -- Hits here when the 31st bit needs to be checked. We do it here

      -- since if we multiply by 2, the number we get is 2147483648, which

      -- results in an overflow

      SET @bitMask = 2147483647

      IF ((@originalNumber & @bitMask) = @bitMask)

      BEGIN

                   Insert Into @BitTable (BitCodeId) Values (@BitMask)              

      END

END

So the While loop finds all the BitWiseIds that comprise the OptionId number and inserts them into a table.  So what we do then is Cross Join the OptionId on our Car table with our bitwise table and we get the options associated with that join.

Let’s refresh our example tables:

“Option” Table

 16          Cold Air Intake

“Car” table

CarId     Description  OptionId

1              Blue Car               5

2              Red Car               26

“Cross Joining Car Table with BitTable from T-SQL snipped, above.

SELECT Distinct c.CarId

, c.Description

, b.BitCodeId

FROM Car c

Cross join @BitTable b

where  b.BitCodeId & c.OptionId = b.bitcodeid

Yields:

CarId     Description         OptionId

1              Blue Car               1

1              Blue Car               4

2              Red Car                2

2              Red Car                8

2              Red Car               16

So now we have a concise and effective way of applying multiple options to a table using a single, flexible bitwise number.