Dear Colleagues!

There is some information about optimization in SQL 2000 and 2005 (may be it can save some time for you in future).

One our client has table with 3 columns: PropertyID INT, PropertyTypeID INT, Details nvarchar

When PropertyTypeID=3, then Details contains decimal number (we know it is not good solution, but it is not our - from another developer).

So task was: get all PropertyID on some conditions. First case for SQL2000 (our dev server) works good:

SELECT *
FROM {databaseOwner}{objectQualifier}Properties
WHERE  (pp.PropertyID IN
  (SELECT PropertyID
  FROM {databaseOwner}{objectQualifier}PropertyDetail
  WHERE 
   (((PropertyTypeID=3) AND (CAST(Details AS decimal(12,2))<=@MaxPrice)) OR (@MaxPrice=-1))
  ))

BUT SQL 2005 causes error "error convert nvarchar to numeric" :( Same code, ok for 2000 and bad for 2005. Have spent additional time and here is another code (with small changes only):

SELECT *
FROM {databaseOwner}{objectQualifier}Properties
(pp.PropertyID IN
  (SELECT PropertyID
  FROM (SELECT * FROM {databaseOwner}{objectQualifier}PropertyDetail WHERE (PropertyTypeID=3)) pd
  WHERE ((CAST(pd.Details AS decimal(12,2))<=@MaxPrice) OR (@MaxPrice=-1)))
  )

You can see query has been changed, so we receive all records with good data to convert and only then verify it with condition.

Hope it will help someone in future.

Sergey