September 19, 2010 at 10:00 pm
We have recently upgraded from 2000 to 2005 and the following issue has occurred with one of our SP's.
This worked fine in 2000;
"(@ReferenceNumber is NULL OR ( ISNUMERIC(P.ReferenceNumber) > 0 AND CAST(P.ReferenceNumber as float) <= CAST(@ReferenceNumber as float)) ) AND"
However, when we upgraded the following was being returned;
Msg 8114, Level 16, State 5, Procedure spd_PaymentSummaryByCashierReportSearchSelect, Line 61
Error converting data type varchar to float.
To get this SP to work I created a new var called @Reffloat and cast outside the select and where clause, like so;
"SET @Reffloat=cast('120387' as float)"
Then change the Where clause like so;
"(@ReferenceNumber is NULL OR ( ISNUMERIC(P.ReferenceNumber) > 0 AND CAST(P.ReferenceNumber as float) <= @Reffloat) ) AND"
It now works as expected. Is this a bug in 2005, or am I programming by coincidence?
September 19, 2010 at 11:15 pm
phil.layzell (9/19/2010)
We have recently upgraded from 2000 to 2005 and the following issue has occurred with one of our SP's.This worked fine in 2000;
"(@ReferenceNumber is NULL OR ( ISNUMERIC(P.ReferenceNumber) > 0 AND CAST(P.ReferenceNumber as float) <= CAST(@ReferenceNumber as float)) ) AND"
However, when we upgraded the following was being returned;
Msg 8114, Level 16, State 5, Procedure spd_PaymentSummaryByCashierReportSearchSelect, Line 61
Error converting data type varchar to float.
To get this SP to work I created a new var called @Reffloat and cast outside the select and where clause, like so;
"SET @Reffloat=cast('120387' as float)"
Then change the Where clause like so;
"(@ReferenceNumber is NULL OR ( ISNUMERIC(P.ReferenceNumber) > 0 AND CAST(P.ReferenceNumber as float) <= @Reffloat) ) AND"
It now works as expected. Is this a bug in 2005, or am I programming by coincidence?
I actually wonder if it's more to do with this component:
( ISNUMERIC(P.ReferenceNumber) > 0 AND CAST(P.ReferenceNumber as float)
and if the optimizer is changing the order of testing.
Can you subquery the setup so that (for testing) you use isnumeric(P.ReferenceNumber) > 0 in the inner query and then where clause it with the CAST( @ReferenceNumber AS float) in the outer query, using a force order hint?
This will help you nail down which piece is being problematic.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
September 19, 2010 at 11:32 pm
phil.layzell (9/19/2010)
Is this a bug in 2005, or am I programming by coincidence?
No. Yes. (in that order)
SQL is under absolutely no obligations regarding the order that it evaluates predicates in the where clause. It does not execute left-to-right, it does do position-based short-circuiting under most circumstances.
In fact, you're still relying on the optimiser producing a plan that evaluates in the order you're hoping for it to do. It may for now, but there's no guarantee that it won't pick a different plan next week and result in conversion errors again.
I suspect that a subquery or CTE will be required here in order to fix the order of evaluation.
Also...
http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 20, 2010 at 11:33 am
I've had really bad experiences with ISNUMERIC() not functioning the way I expected it to. For some reason, every time I want to use it, I have to throw it into a sub-query or CTE or temp table to get it to evaluate the "true" or "false" of the value and do all other evaluations after (or outside) that.
Which goes back to what Gail said. SQL Server makes no promises as to processing order. If you want it to process in a certain order, you have to force the issue by the method of coding.
September 20, 2010 at 6:27 pm
You have to be careful with ISNUMERIC because it doesn't mean that the value will CONVERT/CAST to a numeric data type. For example '+' returns 1, nut CAST('+' as INT) throws an error.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
September 21, 2010 at 2:05 am
Jack Corbett (9/20/2010)
You have to be careful with ISNUMERIC because it doesn't mean that the value will CONVERT/CAST to a numeric data type.
It means that the value will cast successfully to one of more of the numeric data types, though which one is usually another matter.
eg '+' will cast successfully to int and to money, but not to numeric or float.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply