Conversion failure varchar to int on a column cast as int

Question

I've created a view called vReceivedEmail which includes a varchar column called RowPrimaryKeyValue. This column usually stores primary key id values, such as 567781, but every now and then has the descriptive text 'Ad hoc message'.

I've set the view up so that it only shows records that would hold the primary key values and then CAST the column as int.

SELECT      CAST(Email.RowPrimaryKeyValue as int)           as DetailID
FROM        MessageStore.dbo.Email Email
WHERE       ISNUMERIC(Email.RowPrimaryKeyValue) = 1

When I test the view, I only get records with the primary key values I expected, and when I look at the column listing for the view in the object explorer, the column is saved at the int data type.

I've then tried to apply the following WHERE clause in a separate query, referencing my saved view:

SELECT      PotAcc.DetailID
FROM        PotentialAccounts PotAcc
WHERE       PotAcc.DetailID NOT IN (
                SELECT      RecEmail.DetailID
                FROM        vReceivedEmail  RecEmail
            )

...but I'm returning the following error:

Conversion failed when converting the varchar value 'Ad hoc message' to data type int.

'Ad hoc message' is data from the column I filtered and converted to int, but I don't know how it's managing to trip up on this since the view explcitly filters this out and converts the column to int.

score 4 · Accepted Answer · answered Jun 19 '17 at 16:23

4

Since you are on SQL Server 2012, how about using try_convert(int,RowPrimaryKeyValue) is not null instead of isnumeric()?

Your view would look like so:

select try_convert(int, RowPrimaryKeyValue) as DetailID
from MessageStore.dbo.Email Email
where try_convert(int, RowPrimaryKeyValue) is not null

In SQL Server 2012 and up: each of these will return null when the conversion fails instead of an error.

answered Jun 19 '17 at 16:23

SqlZim

37,248
6
41
59

Nice. Not used `try_convert` before, so learned something new today. Thanks very much. – Matt Hall Jun 19 '17 at 16:56
Nice catch on the version – S3S Jun 19 '17 at 17:01

S3S · Answer 2 · 2017-06-19T16:29:37.060

Isnumeric() can be deceiving... however I'm betting this is due to SQL Server optimization. Though your view works, that doesn't mean the outer query guarantees that only INT is returned. Instead of using a view... use a CTE and try it. And since you are on 2012, this can all be avoided how SqlZim explained above.

WITH CTE AS(
SELECT      CAST(Email.RowPrimaryKeyValue as int)           as DetailID
FROM        MessageStore.dbo.Email Email
WHERE       ISNUMERIC(Email.RowPrimaryKeyValue) = 1)


SELECT      PotAcc.DetailID
FROM        PotentialAccounts PotAcc
WHERE       PotAcc.DetailID NOT IN (
                SELECT      RecEmail.DetailID
                FROM        CTE RecEmail
            )

Conversion failure varchar to int on a column cast as int

2 Answers2