dealing with legacy bit encoding table

bit_flag_fun.sql

-- sparce matrix of (baz, biz, buz)
CREATE TABLE myLegacyBitFlagTbl(

	baz char(2) NOT NULL,
	biz char(2) NOT NULL,
	
	-- bit flag fields encode buz(int) values
	colA INT NOT NULL, --LSB in big-endian
	colB INT NOT NULL,
	colC INT NOT NULL,
	colD INT NOT NULL,
	colE INT NOT NULL,
	colF INT NOT NULL,
	colG INT NOT NULL, --MSB in big-endian
	
	constraint myLegacyBitFlagTblPK PRIMARY KEY (baz, biz)
);
GO

CREATE TABLE mySourceData (
  baz char(2) NOT NULL,
  biz char(2) NOT NULL,
  buz int NOT NULL
);
GO

CREATE PROCEDURE usp_UpdateBitFlagTbl
	@bAppend BIT = 0,
	@baz CHAR(2) = NULL,
	@biz CHAR(2) = NULL
AS 
BEGIN

	SET NOCOUNT ON;

	CREATE TABLE #mask (
		N INT NOT NULL PRIMARY KEY,
		flag INT
	);
	
	WITH 
		E00(N) AS (SELECT 1 UNION ALL SELECT 1),
        E02(N) AS (SELECT 1 FROM E00 a, E00 b),
        E04(N) AS (SELECT 1 FROM E02 a, E02 b),
        E08(N) AS (SELECT 1 FROM E04 a, E04 b),
	cteTally(N) AS (SELECT ROW_NUMBER() OVER (ORDER BY N) FROM E08)

	INSERT INTO #mask
	SELECT 
		n -1,
		CAST(  POWER(2,n-1) AS BINARY(3)) AS flag
	FROM cteTally
	WHERE N <= 16;
	

	CREATE TABLE #byte (
		N int NOT NULL PRIMARY KEY,
		colA INT,
		colB INT,
		colC INT,
		colD INT,
		colE INT,
		colF INT,
		colG INT
	);

	INSERT INTO #byte 
		VALUES
		(0,1,0,0,0,0,0,0),
		(1,0,1,0,0,0,0,0),
		(2,0,0,1,0,0,0,0),
		(3,0,0,0,1,0,0,0),
		(4,0,0,0,0,1,0,0),
		(5,0,0,0,0,0,1,0),
		(6,0,0,0,0,0,0,1);
		
	UPDATE 
		myLegacyBitFlagTbl
	SET
		colA = (myLegacyBitFlagTbl.colA*@bAppend) | new_values.colA ,
		colB = (myLegacyBitFlagTbl.colB*@bAppend) | new_values.colB ,
		colC = (myLegacyBitFlagTbl.colC*@bAppend) | new_values.colC ,
		colD = (myLegacyBitFlagTbl.colD*@bAppend) | new_values.colD ,
		colE = (myLegacyBitFlagTbl.colE*@bAppend) | new_values.colE ,
		colF = (myLegacyBitFlagTbl.colF*@bAppend) | new_values.colF ,
		colG = (myLegacyBitFlagTbl.colG*@bAppend) | new_values.colG
	FROM (
		SELECT 
			baz,
			biz,
			CAST( SUM( colA * flag ) AS VARBINARY(MAX) ) colA,
			CAST( SUM( colB * flag ) AS VARBINARY(MAX) ) colB,
			CAST( SUM( colC * flag ) AS VARBINARY(MAX) ) colC,
			CAST( SUM( colD * flag ) AS VARBINARY(MAX) ) colD,
			CAST( SUM( colE * flag ) AS VARBINARY(MAX) ) colE,
			CAST( SUM( colF * flag ) AS VARBINARY(MAX) ) colF,
			CAST( SUM( colG * flag ) AS VARBINARY(MAX) ) colG
		FROM (
			SELECT
				baz,
				biz,
				(buz-1)/16 byte_offset,
				(buz-1)%16 bit_offset
			FROM mySourceData
		) AS x
		JOIN #byte on #byte.n = byte_offset
		JOIN #mask ON #mask.n = bit_offset
		GROUP BY 
			baz,
			biz
	) as new_values
	WHERE
		((@baz IS NULL) OR (myLegacyBitFlagTbl.baz = @baz) )AND
		((@biz IS NULL) OR (myLegacyBitFlagTbl.biz = @biz) )
	
END;