CTE Error (nvarchar to numeric)

I'm using a CTE to convert SSRS stored proc's into BO stored proc's, as apparently I can't use temp tables with Business Objects.

I'm having this query:

;WITH cte1
AS
( 
    SELECT  cv.issue  
          , cv.customfield  
          , CAST(STRINGVALUE AS NUMERIC) AS priority_num  
    --INTO    #temp_priority_val  
    FROM    proddb1.customfieldvalue cv WITH (NOLOCK)  
            INNER JOIN proddb1.customfield e WITH (NOLOCK)
                 ON  cv.CUSTOMFIELD = e.id 
                 AND e.cfname = 'Issue Priority')  

,cte2
AS
( 
    SELECT  a.ISSUE  
          , f.customvalue priority_num  
    --INTO    #temp_priority  
    FROM    cte1 a 
            INNER JOIN proddb1.customfieldoption f WITH (NOLOCK)
                 ON  a.CUSTOMFIELD = f.CUSTOMFIELD        
                 AND CAST(a.priority_num AS NUMERIC) = f.id) 

SELECT * FROM cte2

I keep getting an Error converting data type nvarchar to numeric. error on this execution. The priority_num column is an nvarchar column that is CAST in the first CTE. When I execute this within the original statement that had the temp tables it works perfectly fine. It must be something with the CTE scope?

You can't easily control the order in which SQL Server will evaluate the contents of a column that does not use the correct data type (or has mismatched precision). If you try to cast a column that is nvarchar to numeric, even if you have filters that should eliminate all non-numeric values from consideration, SQL Server can still try those first (see Erland's complaint about this on UserVoice). There are cases where you can nest CTEs until the cows come home, but SQL Server will still push or pull that evaluation to a place where you didn't expect it.

As long as aggregates and fulltext functions aren't involved (see here and here), you can force this evaluation before conversion by using a CASE expression. Here is a simplified version of your query with no CTEs:

SELECT cfv.issue,
  priority_num = CONVERT(NUMERIC(something, something), 
    CASE WHEN ISNUMERIC(cfo.customvalue)=1 THEN cfo.customvalue END)
FROM
  proddb1.customfieldvalue AS cfv
  INNER JOIN proddb1.customfield AS cf
  ON cfv.CUSTOMFIELD = cf.id
  INNER JOIN proddb1.customfieldoption AS cfo
  ON cfv.CUSTOMFIELD = cfo.CUSTOMFIELD
  AND CONVERT(NUMERIC(something, something), 
    CASE WHEN ISNUMERIC(cfo.customvalue)=1 THEN cfo.customvalue END) = cfo.id
  WHERE cf.cfname = 'Issue Priority';

You will need to change something, something to the proper precision/scale (or use one of the int types if you don't need decimal places). You should never declare varying types without specifying the length - this blog post is about varchar, but it really applies to all of the types.

If you are on SQL Server 2012 (it's always useful to include information like what version of SQL Server you're using), you can simplify this:

CONVERT(NUMERIC(something, something), 
    CASE WHEN ISNUMERIC(cfo.customvalue)=1 THEN cfo.customvalue END)

to this:

TRY_CONVERT(NUMERIC(something, something), cfo.customvalue)

And it will actually be more reliable (since ISNUMERIC can return 1 and still fail at conversion time for specific types). I blogged about this way back in 2002.

StackExchange Database Administrators Q#64489, answer score: 12