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 > @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.

So it was the damn query plan it kept deciding to use.

The value that it was exploding on was present in the table (which it shouldn't be but that's another problem) but it was associated with a completely different identifier.

The query in question was running a clustered seek on an index that covered the date but not the identifier this caused it to scan the ENTIRE table which is so, so, so wrong for a number of reasons.

Added a proper index for the where clause and the procedure is happily humming away now as it filters on the identifier before going after the date. I wish i could get before / after statistics but i'm pretty sure its running faster now too.

You are obviously getting different query plans and your cast is being tried against values which are filtered out by the where clause, I wouldn’t bother trying to figure out why — whatever the cause, it could obviously happen for a different reason later. Relying upon filtering is unreliable.

The thing to do is to write a query that won’t fail regardless of any index, hint, or statistics.

I think there are 3 ways to do this:

1. Use a temp table/table variable.

   declare @result table (dop varchar);
   Insert into @result
   SELECT FT.DOP  FROM TRACKING_DATA 
   WHERE date > @mydate and identifier = 0000000000
   
   SELECT cast(dop as SMALLINT) dop from @result
   

2. Use a case statement:

   SELECT CAST(case when FT.DOP like '%[^0-9]%' then null else ft.dop end AS SMALLINT) 
   FROM TRACKING_DATA 
   WHERE date > @mydate and identifier = 0000000000
   

3. Use try_cast instead and of cast:

   SELECT try_CAST(FT.DOP AS SMALLINT) 
   FROM TRACKING_DATA 
   WHERE date > @mydate and identifier = 0000000000
   

Personally I would recommend 3 if possible.