CODEWITHSUNDEEP
sqlt-sqlsql-order-by
Wil
Wil

Reputation: 2368

Order of operations in the where

Here is my sql

SELECT * FROM Answers
WHERE QuesID = 1 AND OptionID IN (2,5) AND CONVERT(float, ISNULL(AnswerValue, 0)) < 300

The problem is there is legacy data that is string in OptionID = 6. When I run the above query it gives me

Error converting data type varchar to float.

I confirmed all data in QuesID=1 and OptionId IN (2,5) is all float data. It seems like SQL is doing the math part of this sql first, is there anyway to make it check the OptionID before running the AnswerValue comparison? Or am I completely off base?

Upvotes: 0

Views: 512

Answers (5)

Steve Kass
Steve Kass

Reputation: 7184

If and when you upgrade to SQL Server 2012, you can use TRY_CONVERT. It's proprietary, but it does solve the problem.

SELECT * FROM Answers
WHERE QuesID = 1
AND TRY_CONVERT(int,OptionID) IN (2,5)
AND CONVERT(float, ISNULL(AnswerValue, 0)) < 300

Upvotes: 0

CD Jorgensen
CD Jorgensen

Reputation: 1401

If you want to guarantee order of operations, and performance isn't as big of a concern, you might declare a table variable to store the results of your first two conditions, and then filter the temp table by your final condition. e.g. 

declare @tempAnswers table (quesID int, optionid int, answervalue varchar(50))

insert into @tempAnswers
select * 
from Answers
where QuesID = 1 and OptionID in (2,5) 

select * from @tempAnswers
where convert(float, isnull(AnswerValue, 0)) < 300

Upvotes: 0

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239824

A CASE expression will only evaluate its result expression(s) if the searched conditions match, so you might try:

SELECT * FROM Answers
WHERE
    CASE WHEN QuesID = 1 AND OptionID IN (2,5) THEN
        CONVERT(float, ISNULL(AnswerValue, 0))
    ELSE
        500
    END < 300

For more complex expressions, you may choose to use nested CASE expressions to perform more logic - oftentimes, this will result in you expressing all of the conditions and making the result equal to 0 or 1, and then perform a final outer comparison to 1:

WHERE
1 = CASE WHEN <Condition 1> THEN
        CASE WHEN <Condition 1, Subcondition 1> THEN 0
             WHEN <Condition 1, Subcondition 2> THEN 1
        END
         WHEN <Condition 2> THEN
        CASE WHEN <Condition 2, Subcondition 1> THEN 1
             WHEN <Condition 2, Subcondition 2> THEN 0
        END
    END

Where the two sub-conditions for Condition 1 will only be evaluated if Condition 1 is true (and similarly for Condition 2)

Upvotes: 1

Philip Kelley
Philip Kelley

Reputation: 40359

You can't really control the order in which SQL will "resolve" the clauses in your WHERE clause. I did some quick testing, and if there was a string value entered only for QuesId=3, it worked.

So, yes, as you posted while I was writing this, tossing in

AND isnumeric(AnswerValue) = 1

will handle this, as SQL appears to evaluate it earlier in its process.

Upvotes: 3

Wil
Wil

Reputation: 2368

Figured it out, I just added ISNUMERIC to the SQL and that sorted that out.

Upvotes: 1

Related Questions