Strange behaviour in TSQL function (parameter with int variable or NULL behaves differently)?
I think you're trying to solve for the wrong problem. As I suggested above, I think you're getting different results simply because you currently have different plans (one for the literal and one for the variable). The plan you're currently getting that causes an error is attempting the cast before the rows are filtered out, but both methods could cause an error if the plan that currently "works fine" gets recompiled. 

What you should be doing is using a CASE expression to ensure that only numeric values that can be cast to an INT are actually attempted to be converted. You need to control this explicitly because you can't always rely on SQL Server filtering rows before attempting calculations. 

In 2012 and up, you can simply replace this:

(CONS.IDp = CAST(UFCFD.Value1 AS INT))


With this:

(CONS.IDp = TRY_CONVERT(INT, UFCFD.Value1))


If you also have to support older versions, you can do this (since we know ISNUMERIC() is not enough):

(CONS.IDp = CASE 
  WHEN UFCFD.Value1 NOT LIKE '%[^0-9]%' 
  AND 
  (
    LEN(UFCFD.Value1 &lt; 11 
    OR
    (
      LEN(UFCFD.Value1) = 11 
      AND LEFT(UFCFD.Value1,1) IN ('0','1','2')
    )
  ) THEN CONVERT(INT, UFCFD.Value1) END)


A CASE expression is pretty reliable for forcing SQL Server to evaluate expressions in a particular order, but do note that there are exceptions.