TSQL. Get Bitwise total for many columns in a row

Question

I have a table that has 4 sets of 25 columns in a BIT concept. Actually field is smallint but it is either 0 or 1 in it's data.

Here is my code that is an attempt to get the total for the first group of 25 cols.

Declare @rows int
, @ID uniqueidentifier
, @LocTotal bigint


select @rows =  ( select  count(*) from #t1 )

while @rows > 0
begin
print @rows
-- get that rowID
       select @ID = (select top 1 recid from #t1)
select @LocTotal =
(select top 1
case when cbHPILoc1 = 1 then 1 else 0 end +
case when cbHPILoc2 =  1 then 2 else 0 end +
case when cbHPILoc3 = 1 then  4 else 0 end +
< snip >
case when cbHPILoc25 = 1 then 16777216 else 0 end
as Total
 from  dbo.MyTest_BitMap
where RecKey = @ID
)
       print @ID
print  @LocTotal

My output:

(5 row(s) affected)
5
67A16306-B27D-4882-88A2-1146CBAAA8D9

(1 row(s) affected)
4
F94929B9-3DA7-4AA3-96F6-728EF025B21C

I fail to get the Total to @LocTotal

TIA

Answer 1

Why so complicated?

SELECT
  RecKey,
  cbHPILoc1
  + cbHPILoc2 * 2
  + cbHPILoc3 * 4
  + ...
  + cbHPILoc25 * 16777216
  AS Total
FROM
  dbo.MyTest_BitMap
WHERE 
  RecKey = @ID

Answer 2

I think Tomalak has it, which is why I modded him up, but if you do this more or once, since all that mess:

cbHPILoc1
  + cbHPILoc2 * 2
  + cbHPILoc3 * 4
  + ...
  + cbHPILoc25 * 16777216

is tedious and error prone, why not do it once as a view and test that thoroughly?

Then just select total from viewname where reckey = ?.

Answer 3

I wager that at least one of your bit columns contains NULL's, which would prevent anything from showing up when you print @LocTotal.

Answer 4

Tomalak as well as tpdi came up with a better way to handle this.

I like the idea of the view.

I do want to toss the 100+ columns and replace with 4, but we can start to see if my java programmer can work with the first part breaking out the values.

I can write the SP on my side to take his new value(S) and repopulate the data properly and then just stick with the bitwise cols in the future.

Thanks.