sql server 2008 - CTE returning error

Question

I wrote a CTE to remove non numeric values from a data set, then get a count of numeric values within a range.

WITH    dtr
      AS ( SELECT   resultlevel r
           FROM     dbo.Result
           WHERE    DrugID = 'AMP'
                    AND ISNUMERIC(ResultLevel) = 1
                    AND AuditStamp > '1/1/2016'
                    AND DeleteFlag = 0
         )
SELECT  COUNT(*)
FROM    dtr
WHERE   CONVERT(INT, r) BETWEEN 50 AND 75

This returns an error in SMS

Msg 245, Level 16, State 1, Line 2
Conversion failed when converting the varchar value 'PND            ' to data type int.

This error is completely possible without the 'dtr' query in the CTE.

When I rewrite this, instead of a CTR, but a TEMP table, it works.

SELECT  resultlevel r
INTO    #d
FROM    dbo.Result
WHERE   DrugID = 'AMP'
        AND ISNUMERIC(ResultLevel) = 1
        AND AuditStamp > '1/1/2016'
        AND DeleteFlag = 0

SELECT  COUNT(*)
FROM    #d
WHERE   CONVERT(INT, r) BETWEEN 50 AND 75

So my questions is why?? I have always thought a CTE was like creating a TEMP table.

TEST DATA

if object_id('tempdb..#temp') is not null drop table #temp

create table #temp (result char(5)) 
insert into #temp (result) values 
('1'),('A'),('>2'),('PEN ') ,('@3'),('-2'),('-33') 


;with isnum AS ( 
SELECT result 
FROM #temp 
WHERE ISNUMERIC(result) = 1)

--Selecting from the CTE yields 1, -2, and -33 all of which can be converted to INT
--Running the query with the where clause causes the conversion error
SELECT 
    result, 
    ISNUMERIC(result)
FROM isnum
--WHERE CONVERT(INT,result) > 1

Answer 1

In SQL Server there is Logical Processing Order of the SELECT statement, which determines when the objects defined in one step are made available to the clauses in subsequent steps:

1. FROM
2. ON
3. JOIN
4. WHERE
5. GROUP BY
6. WITH CUBE or WITH ROLLUP
7. HAVING
8. SELECT
9. DISTINCT
10. ORDER BY
11. TOP

This is how your query is going to be proccesed and your query looks perfectly fine. But sometimes, the SQL Server decides not to follow this order in order to optimize your query.

In your case, the SQL Server might be simplyfing/transforming your query into another and performing the convert function, before applying the where isnumeric filtering.

If we made your query a little more complex (but still giving the same results), the SQL Server is executing the code correctly this time:

;with isnum AS ( 
    SELECT result
    FROM #temp 
    WHERE ISNUMERIC(result) = 1
    GROUP BY result
    HAVING MAX(result) = result
)
SELECT 
    result, 
    ISNUMERIC(result)
FROM isnum
WHERE CONVERT(INT,result) > 1;

In your case (and this is what I am doing in such situations when different types are stored in one column), you can simply use TRY_CONVERT function:

;with isnum AS ( 
SELECT result 
FROM #temp 
WHERE ISNUMERIC(result) = 1)

SELECT 
    result, 
    ISNUMERIC(result)
FROM isnum
WHERE TRY_CONVERT(INT, result) > 1