March 6, 2008 at 11:08 pm
Hello
We are in the process of moving from SQL 2000(SP4) to SQL 2005(SP2), but we are getting some unexpected errors from some stored procs. I've been able to isolate the exact SQL and it is below. When I run this in SQL 2000 it returns back no records as it should. But in SQL 2005 it returns back an error:
Msg 245, Level 16, State 1, Line 29
Conversion failed when converting the varchar value 'VALUE' to data type int.
Has anyone ever seen this? Any help would be greatly appreciated.
Thanks
TSQL to reproduce error:
declare @Apps_AttrValues table (ApplicantID int, Attribute varchar(30), AttrValue varchar(50), CastValueAsType varchar(10))
declare @Rules_AttrValues table (RuleID int,Attribute varchar(30),AttrValue varchar(50),Operator varchar(5))
insert into @Apps_AttrValues
values(1,'NO MATCH','VALUE','VARCHAR')
insert into @Apps_AttrValues
values(2,'NO MATCH','VALUE','VARCHAR')
insert into @Apps_AttrValues
values(3,'NO MATCH','VALUE','VARCHAR')
insert into @Apps_AttrValues
values(4,'NO MATCH','VALUE','VARCHAR')
insert into @Apps_AttrValues
values(5,'NO MATCH','VALUE','VARCHAR')
insert into @Rules_AttrValues
values(2,'match','1','E')
insert into @Rules_AttrValues
values(2,'match','2','E')
insert into @Rules_AttrValues
values(2,'match','3','E')
insert into @Rules_AttrValues
values(2,'match','one','E')
insert into @Rules_AttrValues
values(2,'match','two','E')
insert into @Rules_AttrValues
values(2,'match','three','E')
select RA.RuleID, AA.ApplicantID,AA.AttrValue,RA.AttrValue,AA.CastValueAsType
from @Rules_AttrValues RA
inner join @Apps_AttrValues AA
on AA.Attribute=RA.Attribute
where
(((AA.CastValueAsType= 'INT' and (isnumeric(RA.AttrValue)<>1)) AND
((RA.Operator='E' and cast(AA.AttrValue as int)= cast(RA.AttrValue as int))))
or
(AA.CastValueAsType = 'VARCHAR' AND
((RA.Operator= 'E' and AA.AttrValue= RA.AttrValue))))
March 7, 2008 at 1:54 am
It's when you cast the varchar column as int in the where clause that causes the conversion error.
select RA.RuleID, AA.ApplicantID,AA.AttrValue,RA.AttrValue,AA.CastValueAsType
from @Rules_AttrValues RA
inner join @Apps_AttrValues AA
on AA.Attribute=RA.Attribute
where
(AA.CastValueAsType= 'INT' and isnumeric(RA.AttrValue)<>1 AND
RA.Operator='E' and AA.AttrValue = RA.AttrValue)
or
(AA.CastValueAsType = 'VARCHAR' AND
RA.Operator= 'E' and AA.AttrValue= RA.AttrValue)
..when removed, it parses through, and returns no rows (as expected?)
Though I can't tell if it's logic still 'works as intended' for you.
However, this is one of the things that 2005 is much pickier about than 2000 - type conversions.
What you did was cast a column to a datatype where there was at least one row of data with a value that could not be converted to that type.
It seems that this is how it works, I haven't digged deeper into the details as to why or the reasons behind it.
Thing is that it's of no help to try to 'avoid by filtering', it seems that the mere existence of 'illegal conversion data' is enough, even though those rows may lie outside of the criteria.
/Kenneth
March 7, 2008 at 7:08 am
I agree the issue is with the type conversion:
RA.Operator='E' and cast(AA.AttrValue as int)= cast(RA.AttrValue as int
I'm just confused as I would think it would never process that line, the clause before that:
AA.CastValueAsType= 'INT' and (isnumeric(RA.AttrValue)<>1
Would prevent any processing that would cause the type conversion error.
I guess what is happening is in SQL 2000 it processed the first part of the AND clause and it didn't process the second part of the AND that didn't meet the criteria, so the type conversion error never hit.
But in SQL 2005 it appears to process the entire statement all parts of the AND and hits the error.
Can anyone confirm this? Or have any other ideas?
Thanks
March 7, 2008 at 3:20 pm
Very interesting indeed. It does appear however that SQL Server 2005 will implicitly "convert" your varchar to an int without you specifically casting it (I'm not sure if SQL Server 2000 pre-SP4 had the same behavior, as it appears to work the same with SP4).
Check out the latest BOL chart:
http://msdn2.microsoft.com/en-us/library/ms187928.aspx
If you simply remove the explicit cast you will get back no rows (as you expected) and no error from SQL Server. I'm not sure that this behavior is what you want once you do have matching values in your T-SQL though.
March 7, 2008 at 4:33 pm
tim del bosco (3/7/2008)
I'm just confused as I would think it would never process that line, the clause before that:AA.CastValueAsType= 'INT' and (isnumeric(RA.AttrValue)<>1
Would prevent any processing that would cause the type conversion error.
I guess what is happening is in SQL 2000 it processed the first part of the AND clause and it didn't process the second part of the AND that didn't meet the criteria, so the type conversion error never hit.
But in SQL 2005 it appears to process the entire statement all parts of the AND and hits the error.
Can anyone confirm this? Or have any other ideas?
Thanks
The problem is that you are assuming a certain order of evaluation and then inferring logical "short-circuit"-ing as a result of it, but that is in no way guaranteed by SQL Server. In 2005 it is evaluating things in a slightly different order than in 2000 and that is resulting in this error. See this link for more: http://weblogs.sqlteam.com/jeffs/archive/2008/02/22/sql-server-short-circuit.aspx
Some languages have special logical operators to deal with this, like the "ANDALSO" and "ORELSE" operators in vb.net 2005. In SQL Server 2005, the easiest way to work around it is by nesting CASE functions, which looks rather obtuse, but should work:
select RA.RuleID, AA.ApplicantID,AA.AttrValue,RA.AttrValue,AA.CastValueAsType
from @Rules_AttrValues RA
inner join @Apps_AttrValues AA
on AA.Attribute=RA.Attribute
where
(case when ( AA.CastValueAsType='INT'
and isnumeric(RA.AttrValue)<>1)
AND RA.Operator='E' )
THEN case when cast(AA.AttrValue as int)= cast(RA.AttrValue as int)
THEN 1
ELSE 0 END
ELSE 0 END) = 1
or
(case when (AA.CastValueAsType = 'VARCHAR'
AND (RA.Operator= 'E')
THEN case when (AA.AttrValue= RA.AttrValue)
THEN 1
ELSE 0 END
ELSE 0 END) = 1
-- RBarryYoung
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply