This is your new blog post. Click here and start typing, or drag in elements from the top bar.
At times when running SQL queries you might get the below error message
Msg 245, Level 16, State 1, Line 1 The conversion of the varchar value ’XXXXXXX′ overflowed an INT column. One of the reaons for these errors could be because of using the CASE Statements. If you find this hard to believe run the below query and you would get the same error as above create table abc (XYZ nvarchar(50)) insert into abc values ('123'),('aaa'),('123-456-78910') select case when patindex('%[a-z]%', xyz)> 0 then 0 else xyz end from abc To fix this error whenencountered while using the case statement select case when patindex('%[a-z]%', xyz)> 0 then 0 else cast(replace(xyz,'-','') as bigint) end from abc If you notice Data type has been casted in the Else part and that is because without it Nvarchar(50) was attempted to convert to INT due to datatype precedence rules and Arithmetic overflow occurs. But after typecasting from nvarchar to BIGINT, BIGINT has higher precedence over INT and the Arithmetic overflow error magically gets resolved Take a look at the below link to see which data types have higher precedence. http://technet.microsoft.com/en-us/library/ms190309.aspx | AuthorSQL DBA ArchivesCategories |