CAST throwing error when run in stored procedure but not when run as raw query
You're better off not depending on the indexing to avoid these errors, and instead writing the query to guard against this situation.  Since you're on SQL Server 2012, one option would be to use TRY_CAST:

SELECT TRY_CAST(FT.DOP AS SMALLINT) 
FROM TRACKING_DATA 
WHERE date &gt; @mydate and identifier = 0000000000;


This will result in NULL being selected for values that fail to convert from varchar to smallint.  But as long as you know there aren't any results like that, or your application can handle the NULL results, you should be good.