Conversion failed when converting nvarchar value to data type int

Hello everyone,

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, 
		@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

Open in new window


What am I doing wrong here?
dsoderstromAsked:
Who is Participating?
 
Duy PhamFreelance IT ConsultantCommented:
The error is not really about the data conversion, but mainly the wrong query to get the actual field value from the table.

When you use:
SELECT @fieldChecked = Convert(int,@colBuffer) FROM CHECKLIST WHERE (personID = 7)

Open in new window

It means that you are trying to set value for @fieldChecked = value of @colBuffer as variable. Hence you got conversion error when trying to convert from NVARCHAR (type of @colBuffer variable) to INT (type of @fieldChecked variable). And in this case 'FROM CHECKLIST WHERE persionID = 7' makes no sense.

Why as I understand, you are trying to get the actual BIT value of the field column named as @colBuffer in table CHECKLIST.
In order to do that, you need to use Dynamic SQL as below:
DECLARE @sql nvarchar(max), 
                @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
                SET @sql = 'SELECT @fieldChecked = ' + @colBuffer + ' FROM CHECKLIST WHERE persionID = 7'
		EXEC sp_executesql @sql, N'@fieldChecked BIT OUTPUT', @fieldChecked OUTPUT
		SELECT @valueBuffer = @valueBuffer + @fieldChecked
		SELECT @intCounter = @intCounter + 1
	END

SELECT @valueBuffer

Open in new window

0
 
OriNetworksCommented:
It means exactly what it says. If you know the value stored in the nvarchar is an integer you can do a convert .

SELECT @fieldChecked = Convert(int,@colBuffer) FROM CHECKLIST WHERE (personID = 7)

Open in new window

0
 
Scott PletcherSenior DBACommented:
Easiest way is to put quotes are around the 7.  Btw, that is also the most efficient if an index exists on that column:

SELECT @fieldChecked = @colBuffer FROM CHECKLIST WHERE (personID = '7')
0
 
dsoderstromAuthor Commented:
Awesome!  This worked perfectly!  Thank you Duy Pham.  Thank you OriNetworks and ScottPletcher for taking the time to look at this as well.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.