By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE Table1
([Form1] int, [Form2] int, [Form3] int, [Form4] int, [Form5] int, [Form6] int, [Form7] int)
;
INSERT INTO Table1
([Form1], [Form2], [Form3], [Form4], [Form5], [Form6], [Form7])
VALUES
(1, 0, 0, 0, 0, 0, 0),
(0, 0, 0, 1, 0, 0, 0),
(0, 0, 0, 0, 0, 0, 1),
(0, 1, 0, 0, 0, 0, 0)
;
4 rows affected
CREATE FUNCTION fnBuildFormType
(
@varbin varbinary
)
RETURNS integer
AS
BEGIN
DECLARE @i INT = 0;
DECLARE @ReturnInteger INT = 0;
WHILE @i <= 7
BEGIN
IF GET_BIT ( @varbin, @i) = 1
BEGIN
SET @ReturnInteger = @i + 1 ;
BREAK;
END
SET @i = @i + 1;
END
RETURN(@ReturnInteger);
End;
SELECT dbo.fnBuildFormType(CONVERT(BIGINT,CONVERT(BINARY(7),1 * [Form1] + 10 * [Form2] + 100 * [Form3] + 1000 * [Form4]
+ 10000 * [Form5] + 100000 * [Form6] + 10000000 * [Form7])))
FROM Table1
| (No column name) |
|---|
| 1 |
| 4 |
| 8 |
| 2 |