November 12, 2003 at 9:38 am
I have a table that contains parameters, these parameters are stored in another table as a binary mask (in decimal).
I am able to return the bit that is set but am having a few problems returning the Nth row from the parameter table to give the description of the parameter. I have catered for a 32bit Binary mask, so there could possibly be 32 rows to return.
any pointers would be usefull
regards
ANDY
November 12, 2003 at 10:29 am
I recommend against doing this. Instead use an association table between the table and its possible parameters. But, if you must, make the ID of the parameter table the bit value (e.g., 1, 2, 4, 8,...) and:
SELECT Table.ID, ParamTable.Descr
FROM Table t JOIN ParamTable p on t.BitMap & p.ID = p.ID
--Jonathan
--Jonathan
November 17, 2003 at 3:52 am
I decided to go with adding a bitmask to the parameter table and have now found the following code to return the bit('s) that are currently set.
DECLARE @originalNumber INT
DECLARE @bitMask INT
DECLARE @counter INT
BEGIN
SET @bitMask = 1
SET @originalNumber = 7
SET @counter = 1
WHILE (@counter < 31)
BEGIN
IF ((@originalNumber & @bitMask) = @bitMask)
BEGIN
PRINT 'Bit Number (' + LTRIM(RTRIM(STR(@counter))) + ') is ON'
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
PRINT 'Bit Number (' + LTRIM(RTRIM(STR(@counter))) + ') is ON'
END
END
Thanks for the help
November 17, 2003 at 5:31 am
quote:
I decided to go with adding a bitmask to the parameter table and have now found the following code to return the bit('s) that are currently set.DECLARE @originalNumber INT
DECLARE @bitMask INT
DECLARE @counter INT
BEGIN
SET @bitMask = 1
SET @originalNumber = 7
SET @counter = 1
WHILE (@counter < 31)
BEGIN
IF ((@originalNumber & @bitMask) = @bitMask)
BEGIN
PRINT 'Bit Number (' + LTRIM(RTRIM(STR(@counter))) + ') is ON'
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
PRINT 'Bit Number (' + LTRIM(RTRIM(STR(@counter))) + ') is ON'
END
ENDThanks for the help
If that's all you need, then how about just using a numbers table, e.g.:
DECLARE @originalNumber bigint
SET @originalNumber = 7
SELECT 'Bit Number ' + CAST(Number AS varchar) + ' is On'
FROM
(SELECT Number, POWER(CAST(2 AS bigint),Number) Pos
FROM master..spt_values
WHERE Type = 'p'
AND Number < 32) n
WHERE Pos & @originalNumber = Pos
Your looping code will not be easily extensible to returning the settings for a set of rows...
--Jonathan
Edit-- just noticed you're numbering your bits starting with 1, not 0. Change to CAST(Number + 1 AS varchar).
Edited by - Jonathan on 11/17/2003 06:43:31 AM
--Jonathan
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply