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 the
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 the
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.

  Wayne

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

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.  But
the behavior change between 2000 and 2005 was sure a surprise.  Thanks again
for your help.

  Wayne

Show quoteHide quote

Sql Server 2005 - SMS "Modify Table option" times out when trying to 'alter' very large table
SQLExpress
Rename Cluster Instance - New Hardware - Keep Name
Moving the SQL 2005 System Tables
Microsoft doesnt really support anything over 32 Databases(??)
Database refresh
linked server to Excel
SQL Server CPU Spikes
Object search query???
server feedback