CODEWITHSUNDEEP
sqlselect
Kalaan
Kalaan

Reputation: 3

SQL - Why does the ordering matter for this select?

I ran the below code and got the following error;

Conversion failed when converting the varchar value '1.5' to data type int

BEGIN -- first update to check Interface held SMR and Interface held SMA
update interface set INTERR = 'U7'
from interface i
where 
i.conttype = 'SMR' 
and isnumeric(i.contrate)=1 
and cast(i.contrate as decimal(12,2)) < 5 
and caseno = @caseno
and exists (
    select 1 
    from interface i2 
    where i2.caseno = @caseno 
    and i2.conttype = 'SMA' 
    and i2.intmembno = i.intmembno 
    and i2.effdte = i.effdte 
    and i2.contrate > cast(0 as decimal(12,2)) 
    and isnumeric(i2.contrate)=1
)

In this example SMR = 5 and SMA = 1.5 and both values have been declared as Numerics. However by switching around the ordering of the clauses the error stops occuring and the stored procedure continues on as it should (see below)

BEGIN -- first update to check Interface held SMR and Interface held SMA
update interface set INTERR = 'U7'
from interface i
where 
i.conttype = 'SMR' 
and isnumeric(i.contrate)=1 
and cast(i.contrate as decimal(12,2)) < 5 
and caseno = @caseno
and exists (
    select 1 
    from interface i2 
    where i2.caseno = @caseno 
    and isnumeric(i2.contrate)=1 -- This was moved up
    and i2.conttype = 'SMA' 
    and i2.intmembno = i.intmembno 
    and i2.effdte = i.effdte 
    and i2.contrate > cast(0 as decimal(12,2))
)

Can you help me understand why the ordering matters? Normally it doesn't and shouldn't, as far as I know.

Thanks! Paul

Upvotes: 0

Views: 109

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269793

WHERE conditions are not executed in any particular order. This is even true when using subqueries and CTEs -- the optimizer rearranges processing and for good reason.

And, using implicit conversion is dangerous -- as you are finding. So, use explicit conversions and do:

where i.conttype = 'SMR' and
      try_cast(i.contrate as decimal(12,2)) < 5 and
      caseno = @caseno and
      exists (select 1 
              from interface i2 
              where i2.caseno = @caseno and
                    i2.conttype = 'SMA' and
                    i2.intmembno = i.intmembno and
                    i2.effdte = i.effdte and
                    try_cast(i2.contrate as decimal(12,2)) > 0
             )

Notes:

  • You do not have to check isnumeric().
  • There is no need to cast a constant such as 0 for the comparison.
  • You can do similar things with a case in pre-2012 versions of SQL Server.

Upvotes: 2

Related Questions