DECLARE @fieldChecked int, @colBuffer nvarchar(50), @intCounter int, @totalRows int, @valueBuffer int -- holds the cumulative total of bits that are marked true in CHECKLIST Table -- Step 1) Get the total number of "checklist items" that need to be done throughout the buying process -- Each checklist item is a 'bit' field in the CHECKLIST table. This easy to get with the following query SELECT @totalRows = Count(Data_type) FROM information_schema.columns WHERE data_type = 'bit' AND Table_Name = 'CHECKLIST' -- Step 2) put all the checklist items in a temporary table DECLARE @temp TABLE ( Row int, Column_Name nvarchar(50) ) INSERT INTO @temp SELECT row_number() OVER(ORDER BY Column_name) as Row, Column_name FROM information_schema.columns where data_type = 'bit' AND Table_Name = 'CHECKLIST' -- Set up a couple variables for the loop. SET @intcounter= 1 -- @intCounter will hold the row number in the @temp table SET @valueBuffer = 0 -- @valueBuffer will hold the number of checklist items that are marked as complete -- begin loop to find how many fields are marked true WHILE (@intCounter <= @totalRows) BEGIN SELECT @colBuffer = (SELECT Column_Name FROM @temp WHERE Row = @intCounter) -- the next line produces a 'Conversion failed when converting the nvarchar value to data type int. -- it is referring to the variable held in @colBuffer SELECT @fieldChecked = @colBuffer FROM CHECKLIST WHERE personID = 7 SELECT @valueBuffer = @valueBuffer + @fieldChecked SELECT @intCounter = @intCounter + 1 END SELECT @valueBuffer
From novice to tech pro — start learning today.