In the code below I am trying to write a small routine in SQL Server that loops through all the columns in a table that hold 'bit' values. Each field corresponds to a checklist item on an MS Access form.
The problem is with this line:
SELECT @fieldChecked = @colBuffer FROM CHECKLIST WHERE (personID = 7)
I keep getting this error on line 35:
Conversion failed when converting the nvarchar value 'XXXX' to data type int.
'XXXX' is the value held in @colBuffer, which is a field name in the CHECKLIST table.
DECLARE @fieldChecked 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)
WHERE data_type = 'bit' AND Table_Name = 'CHECKLIST'
-- Step 2) put all the checklist items in a temporary table
DECLARE @temp TABLE
INSERT INTO @temp
SELECT row_number() OVER(ORDER BY Column_name) as Row, Column_name
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)
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
What am I doing wrong here?