Question: Teasing apart a bit-field and storing the results in table rows

Question

Question: Teasing apart a bit-field and storing the results in table rows

Gerry Roston

Right there with Babe

Points: 786
More actions
March 15, 2015 at 9:52 am

#398186

I am developing a system that has hardware devices in the field, with each hardware device having a number of active channels. The hardware devices know which of their channels are active and communicate this information to the web-based server. Each device can have (currently) a maximum of 16 channels, thus I send the information to the web-based server as a 16 bit integer, with a 1 indicating that the channel at the location is active and a 0 meaning that it is inactive. (In the examples below, this integer is called @ConfigValue.)
In my database, I have a table whose three columns of interest are DeviceID, ChannelID, and IsActive. I am struggling to figure out how to convert the packed integer into the rows of the table. For purposes of this discussion, we can assume that all 16 rows for a particular device are already in the database, the goal is to simply set the IsEnabled bit.
The easy approach is to use a cursor. However, cursors should be avoided at all costs (or so I have been led to believe) unless they are absolutely needed.
I have tried two approaches, but neither worked. In both cases, I first set IsEnabled to 0 for all channels.
First try:
UPDATE DeviceTypes
SET IsEnabled = 1
WHERE DeviceID = @DeviceID AND (@ConfigValue & POWER(2, ChannelID) = 1)
When I tried this, if @ConfigValue is greater than zero, then ChannelID 0 is set to 1 and all others are unchanged.
Second try:
UPDATE DeviceTypes
SET IsEnabled = CASE WHEN (@ConfigValue & POWER(2, ChannelID)) = 1 THEN 1 END
WHERE DeviceID = @DeviceID
When I tried this, regardless of the value set for @ConfigValue, the value for IsEnabled for all channels is set to NULL.
I look forward to your suggestions for solving this problem. Thank you.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 1

As you know, the "&" is a bit mask for a bit-wise AND. If we take the simple problem of the number number "15" for the Config Value and try to compare that to the number "2", we end up with...

00001111

00000010 AND

---------

00000010

... which isn't a "1" like you're looking for.

I suspect you might need something more like the following...

UPDATE DeviceTypes

SET IsEnabled = 1

WHERE DeviceID = @DeviceID AND (@ConfigValue & POWER(2, ChannelID) > 0)

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

Gerry Roston Right there with Babe Points: 786 More actions · Answer 2

Gerry Roston

Right there with Babe

Points: 786

March 15, 2015 at 1:40 pm

#1784124

Doh!

ScottPletcher SSC Guru Points: 100262 More actions · Answer 3

Yep; much safer to use "> 0" rather than checking for the specific bit value 🙂

SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!

Sporidium SSCommitted Points: 1550 More actions · Answer 4

It's even safer to use "<> 0".

If @ConfigValue is a smallint, then when channel 15 is enabled the masked value of 0x8000 will be interpreted as a negative value.