2

Here's an example query:

DECLARE @table table (loc varchar(10))

INSERT INTO @table VALUES
('134a'), ('123'), ('abc'), ('124')

SELECT * 
FROM (
    SELECT * FROM @table WHERE ISNUMERIC(loc) = 1
) as a
WHERE CAST(loc as INT) BETWEEN 100 AND 200

If I have some varchar values and I limit them to numeric values using ISNUMERIC in a derived table in the query, why does it result in a conversion error?:

Conversion failed when converting the varchar value '134a' to data type int.

Is there a way around this?

MikeM
  • 27,227
  • 4
  • 64
  • 80
  • possible duplicate of [SQL Server 2008: Error converting data type nvarchar to float](http://stackoverflow.com/questions/9136722/sql-server-2008-error-converting-data-type-nvarchar-to-float) – GSerg Feb 06 '12 at 22:58
  • To clarify: it doesn't matter you have that constraint as a subquery. SQL Server unwound it because it easily could. – GSerg Feb 06 '12 at 22:59
  • 1
    @GSerg Can you elaborate on "because it easily could"? – MikeM Feb 06 '12 at 23:02
  • 1
    SQL Server analyzes the query before execution to build an execution plan. This process is quite deep and nice. What can be unwound and flattened, usually is. This includes subqueries and referenced views, and inline table-valued functions. So see the linked question for a workaround. – GSerg Feb 06 '12 at 23:09
  • @GSerg Thanks, `CASE` expression will probably be the easiest workaround. – MikeM Feb 06 '12 at 23:15

1 Answers1

5

The WHERE clause executes first. Try:

DECLARE @table table (loc varchar(10)) 

INSERT INTO @table VALUES 
('134a'), ('123'), ('abc'), ('124') 

SELECT *  
FROM ( 
    SELECT * FROM @table
) as a 
WHERE ISNUMERIC(loc) = 1 and CAST(loc as INT) BETWEEN 100 AND 200
HABO
  • 15,314
  • 5
  • 39
  • 57
  • Is there any way to be sure `ISNUMERIC(loc)` will be executed before `CAST(loc as INT) BETWEEN 100 AND 200`? – MikeM Feb 07 '12 at 05:00
  • SQL Server guarantees that operators of equal precedence will be evaluated left to right, but there is no guarantee regarding short circuit evaluation of boolean expressions. Moreover, it appears that the behavior may vary in different contexts, e.g. `IF`, `CASE` and `WHERE`. I wish I could cite an authoritative reference the would put this to bed. Or that they would support explicit `AND_THEN` and `OR_ELSE` operators like some other languages. – HABO Feb 07 '12 at 13:59