Insert problem: conversion of varchar overflowed int

[sagn]

Hi,

I am trying to insert into a table (tableB)
with columns:
col1 varchar(15) and code varchar(8000)

Col1 is a simple copy from tableA while
col2 is a field created by a concatentaiont of values
from tableA.

The insert statement looks like
BEGIN CODE:
insert into tableB
select col1,
case
when col2=1
then '002'
else ''
end
+
case
when col3=1
then '003'
else ''
end etc...
as code
from tableA
END CODE

The case statement I created as 2 strings (@str2,@str3)
Both variables are varchar(8000). (The number of case
statements is considerable)

My exec statement looks like


exec('Insert into tableB select top 100 col1,'+@str2+@str3+' as code from tableA')

I get the following error:

Server: Msg 248, Level 16, State 1, Line 1
The conversion of the varchar value '008022029053080099100135' overflowed an int column. Maximum integer value exceeded.
The statement has been terminated.


Why? Nothing I have is defined as an int. I checked
the table and there are no int columns. Why is this happening?

Thanks a mil'

ds

 

[fluteplr]

when col2=1


if col2 is not an int why are you checking to see if it equals the integer value 1?

sql server is trying to convert col2 to an integer so it can compare it to the value 1

 

[sagn]

In tableA col2 is an int.

but it is ONLY ever a value of 1 or 0 (not the
very long value you see in the error)
the value it says it is trying to convert is
the result of
the string concatenation of
'001'+'002' (for example).
Those parts are string values.

I can compare col2 with an int, because col2
is an int. That is not why the error is occuring
I am 99% sure of that.


Any other thoughts?

ds

 

[sagn]

I found the problem.

For those that are interested:
As it turns out at the end of my case statement string
(created out of the while loop since
it was the last part of the statement)
there was not a set of single quotes around the
value to be added to the string to be inserted.

thanks for your thoughts

ds