Peter Scheurer (scheurer@gmx.com) writes:[color=blue]
> I have a statement that works in compatibility mode 8 of SQL Server
> 2000 while it fails in 7:
>
> declare @P3 int
> exec sp_prepexec @P3 output, N'@P1 float', N'select custname from
> customer where custnr = @P1', 12600034
>
> custnr is a varchar(15). As long as the float p1 is less than
> 1,000,000 the query returns correct results, but as soon as p1 is
> equal or greater than 1,000,000 the resultset is empty. I could
> figure, that above 1 million, sp_exec converts the float into
> exponential format and compares this format to the string containing
> fixed format values. As said above, in mode 8 the statement returns
> correct results above 1,000,000.
> Please don't ask me, why we use float variables - I don't have any
> influence on the statement - we isolated the statement with the
> profiler.
> We are also currently dependent on the compatibility mode 7.[/color]
The issue is probably that the rules for implicit conversion are
different in SQL7 and SQL2000, and this is reflected in the compatibility
modes. In mode 80, the varchar column is converted, which means that you
may get a hit (float comparisons is always a lottery game), but you will not
use the index. In level 70, the float value is converted to varchar.
This is a poor design from the start, and I'm afraid that the only
workaround I can think of is to redesign.
--
Erland Sommarskog, SQL Server MVP,
esquel@sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp 
