T-SQL selecting values that match ISNUMERIC and also are within a specified range. (plus Linq-to-sql)

Question

I am trying to select rows from a table where one of the (NVARCHAR) columns is within a numeric range.

SELECT ID, Value
FROM Data
WHERE ISNUMERIC(Value) = 1 AND CONVERT(FLOAT, Value) < 66.6

Unfortunately as part of the SQL spec the AND clauses don't have to short circuit (and don't on MSSQL Server EE 2008). More info: Is the SQL WHERE clause short-circuit evaluated?

My next attempt was to try this to see if I could achieve delayed evaluation of the CONVERT

SELECT ID, Value
FROM Data
WHERE (CASE WHEN ISNUMERIC(Value) = 1 THEN CONVERT(FLOAT, Value) < 66.6 ELSE 0 END)

but I cannot seem to use a < (or any comparison) with the result of a CONVERT. It fails with the error

Incorrect syntax near '<'.

I can get away with

SELECT ID, CONVERT(FLOAT, Value) AS Value
FROM Data
WHERE ISNUMERIC(Value) = 1

So the obvious solution is to wrap the whole select statement in another SELECT and WHERE and return the converted values from the inner select and filter in there where of the outer select. Unfortunately this is where my Linq-to-sql problem comes in. I am filtering not only by one range but potentialy by many, or just by the existance of the record (there are some date range selects and comparisons I've left out.)

Essentially I would like to be able to generate something like this:

SELECT ID, TypeID, Value
FROM Data
WHERE (TypeID = 4 AND ISNUMERIC(Value) AND CONVERT(Float, Value) < 66.6) 
      OR (TypeID = 8 AND ISNUMERIC(Value) AND CONVERT(Float, Value) > 99)
      OR (TypeID = 9)

(With some other clauses in each of those where options.) This clearly doesn't work if I filter out the non-ISNUMERIC values in an inner select.

As I mentioned I am using Linq-to-sql (and PredicateBulider) to build up these queries but unfortunately

Datas.Where(x => ISNUMERIC(x.Value) ? Convert.ToDouble(x.Value) < 66.6 : false)

Gets converted to this which fails the initial problem.

WHERE (ISNUMERIC([t0].[Value]) = 1) AND ((CONVERT(Float,[t0].[Value])) < @p0)

My last resort will have to be to outer join against a double select on the same table for each of the comparisons but this isn't really an idea solution. I was wondering if anyone has run into similar issues before?

Solution

I dont think the issue is the AND itself, but rather the convertion from NVARCHAR to FLOAT

Have a look at the following example

DECLARE @Table TABLE(
        Value NVARCHAR(10)
)

INSERT INTO @Table SELECT '1'
INSERT INTO @Table SELECT '100'
INSERT INTO @Table SELECT 'A'

SELECT  *
FROM    @Table
WHERE   ISNUMERIC(Value)= 1 AND CAST(CAST(Value AS VARCHAR(10)) AS FLOAT) > 50

SELECT  *
FROM    @Table
WHERE   ISNUMERIC(Value)= 1 AND CAST(Value AS FLOAT) > 50

The last select is where I get the error stating

Msg 8114, Level 16, State 5, Line 13 Error converting data type nvarchar to float.

But the first select works fine.

Comments

[Toby]

That's amazing, thank you so much. But why is this causing problems?

[iadvd]

@AdriaanStander I was getting crazy with this problem too. You saved my day (seven years later!). I do not understand why there is a need to re-cast the already existing NVARCHAR again to a NVARCHAR before casting then to a FLOAT (in my case the problem was with a VARCHAR and converting to INT but it is exactly the same problem).