SQL: the conversion of the varchar value OVERFLOWED and int column

The max value you can get in an INT is 2147483647

Have a look at the article: The conversion of the varchar value overflowed an int column

Thank you very much for your help and how can I get a workaround for this?

Thank you very much for your help.

Look at the article, there is a solution marked in green. It seems like they suggested using BIGINT. See Difference between INT and BIGINT data type in Sql Server.

INT max = 2,147,483,647
BIGINT max = 9,223,372,036,854,775,807

Just to 'check the box', this begs the question: Why is a column that stores numbers defined as a varchar in the first place, especially if it is expected to participate in JOIN clauses with numeric columns?

varchar's take more memory to store the value, and it allows for non-numeric values to be entered that would cause a JOIN on a numeric column to fail.

In your code the below two columns need to be the same data type, and one is a varchar and the other is an int, so SQL Server attempted implicit conversion to CAST the varchar as an int, and one of the values was beyond the acceptable range of an int.  That's why the error message appeared.

P.SPoTInternalID = EPM.NWSMGR_OLD_INTERNAL_Id

Select allOpen in new window

Assuming the value cannot change, the only real options you have are to CAST the other numeric column as a varchar so that the JOIN is an 'apples-to-apples' comparison, or Dirk's solution above to CAST both columns as a BIGINT.

What are the data types from the columns in the JOIN clause:

@Vitor:

Please find data type of both columns:

SPoTInternalID (varchar (255), null)
EPM.NWSMGR_OLD_INTERNAL_Id (int,(4),null)

Thank you very much for your help.

Please, NO points to me for just this, points to Vitor, I'm just doing a quick follow-up on his posts since I've already read the q.

I added the table alias to the Version and Active columns just because it's proper form.  If either/both those columns are not from the "P" table, you'll have to adjust the LEFT JOIN conditions;

SELECT
P.SPoTInternalID,
P.Id,
EPM.NWSMGR_OLD_INTERNAL_Id
FROM mstt_schema.Project P
LEFT JOIN [RDITS-BI-QA1].[ECubeDWH].[dwh].[REF_NWS_Liste_Projects_Migrated] EPM
ON (P.SPoTInternalID <= 2147483647) AND P.SPoTInternalID = EPM.NWSMGR_OLD_INTERNAL_Id
WHERE (P.[Version] = '1') and (P.Active = '1')

If the SPoTInternalID column is not the PK for that table I would alter it as bigint:

alter table mstt_schema.Project alter column SPoTInternalID bigint

Select allOpen in new window

This way you can keep the query as is and without having to CAST or to use filters on the value.

It's far more efficient to just filter out values that are too large rather than to convert the column, because the conversion makes any index seeks on the index impossible.

If the value never exceeds an int, there's also no real reason to waste another 4 bytes storing it, simply exclude impossible values from the join.

@Scott, looks like there are implicit conversions (varchar --> int) that will act as if you're using the CAST function explicitly.

Thank you very much for your help.

The following solution works for me:

CAST(P.SPoTInternalID AS BIGINT) = EPM.NWSMGR_OLD_INTERNAL_Id

Select allOpen in new window

Thank you again for your help.

@Vitor:

That's true, a good point.  Assuming leading zeros are not allowed in the varchar column, the technique below would be a more efficient and far safer way to test the column.  LEN() is perhaps not sargable, but it's a very efficient check and won't require converting the column to a numeric value.

LEFT JOIN [RDITS-BI-QA1].[ECubeDWH].[dwh].[REF_NWS_Liste_Projects_Migrated] EPM
ON (LEN(P.SPoTInternalID) <= 9 OR (LEN(P.SPoTInternalID) = 10 AND P.SPoTInternalID <= '2147483647')) AND P.SPoTInternalID = EPM.NWSMGR_OLD_INTERNAL_Id

@Scott,

Tell us about the performance of that query in a scenario involving millions of rows. :)

What method would perform better?  Moreover, the method will not fail, even if nonnumeric data beyond the length of an int value gets in the column.  Any method that casts to bigint will automatically fail in a large, non-numeric value is present in the column.

I understand that with this particular "design" there is not much you can do. :o)