Difference between 2000 and 2005. Maybe a bug?

I’m having a problem with a piece of sql and the way it is interpreted in
SQL
Server 2000 and SQL Server 2005. Here is a table definition:
if not exists
(select 1 from sysobjects where uid = user_id() and type = 'U' and name
= 'rpt_Parameter')
CREATE TABLE rpt_Parameter
(
idrpt_Parameter integer IDENTITY,
idrpt_UserReport integer NOT NULL,
szParameterName nvarchar(255) NOT NULL,
szParameterValue nvarchar(2000) NOT NULL
)
go
if not exists
(select 1 from sysobjects o, information_schema.constraint_table_usage c
where o.uid = user_id() and o.type = 'K' and o.name = 'rpt_Parameter_KEY'
and o.name = c.constraint_name and c.table_name = 'rpt_Parameter' and
c.table_schema = user_name())
ALTER TABLE rpt_Parameter ADD
CONSTRAINT rpt_Parameter_KEY
PRIMARY KEY CLUSTERED (idrpt_Parameter)
Go
As part of a software upgrade we execute the following SQL
Update rpt_parameter
Set szParameterValue = '999999999999'
Where szParameterName in ('AdjCostEnd', 'ChargeCostEnd')
And convert(decimal(16,4),szParameterValue) > 999999999999
GO
In SQL Server 2000 this executed fine. In SQL Server 2005 I get the
following error:
“Error converting data type nvarchar to numeric”
Everywhere in the table where the szParameterName meets the values in the
where clause the values are indeed numeric but there are other entries where
they are not. It seems like the DB engine is not performing that part of th
e
where before doing the convert which results in the error. I rechecked BOL
and the precedence should be left to right. I’ve tried reworking the
statement several ways even adding an extra IsNumeric check and enclosing th
e
entire where clause in parenthesis but the result is always the error. I
have been able to get around it by first selecting the correct rows based on
the szParameterName value into an inline table and then joining on that for
the convert but it seems really clumsy to do that.
Could this be a bug? I searched the forum for similar things and did find
one post about a date conversion problem but didn’t find anything else?
Thanks in advance for any help.
WayneOn Thu, 19 Jan 2006 13:09:04 -0800, Wayne wrote:
(snip)
>As part of a software upgrade we execute the following SQL
>Update rpt_parameter
>Set szParameterValue = '999999999999'
>Where szParameterName in ('AdjCostEnd', 'ChargeCostEnd')
>And convert(decimal(16,4),szParameterValue) > 999999999999
>GO
>In SQL Server 2000 this executed fine. In SQL Server 2005 I get the
>following error:
>Error converting data type nvarchar to numeric
Hi Wayne,
This is not a bug. The optimizer is free to evaluate the conditions in
any order it sees fit, just as long as it doesn't affect the output it
produces. Now you may say that in this case it does affect the output,
but situations like this (with data that may throw errors) are
explicitly exempted from that rule.
A possible workaround is to use a CASE, where the order of evaluation is
guaranteed:
WHERE CASE WHEN szParameterName in ('AdjCostEnd', 'ChargeCostEnd')
THEN convert(decimal(16,4),szParameterValue)
ELSE 0.0
END > 999999999999.0
Hugo Kornelis, SQL Server MVP|||Hi Hugo,
Thank you very much. I tried you example and it works, but I bet you knew
that :-) I guess I'll have to remember to think about it in that way when
making a where clause that could have invalid output when evaluating it. Bu
t
the behavior change between 2000 and 2005 was sure a surprise. Thanks again
for your help.
Wayne
"Hugo Kornelis" wrote:

> On Thu, 19 Jan 2006 13:09:04 -0800, Wayne wrote:
> (snip)
> Hi Wayne,
> This is not a bug. The optimizer is free to evaluate the conditions in
> any order it sees fit, just as long as it doesn't affect the output it
> produces. Now you may say that in this case it does affect the output,
> but situations like this (with data that may throw errors) are
> explicitly exempted from that rule.
> A possible workaround is to use a CASE, where the order of evaluation is
> guaranteed:
> WHERE CASE WHEN szParameterName in ('AdjCostEnd', 'ChargeCostEnd')
> THEN convert(decimal(16,4),szParameterValue)
> ELSE 0.0
> END > 999999999999.0
> --
> Hugo Kornelis, SQL Server MVP
>