September 3, 2015 at 10:56 am
The conversion of the varchar value '7048884212' overflowed an int column.
Column in question is type "bigint".
INSERT INTO has this statement in the SELECT clause:
CONVERT(bigint, IIF(IsNumeric(CALL_ANI)=1,CALL_ANI,0)) AS ANI
WTF ?
September 3, 2015 at 11:03 am
Try
CONVERT(bigint, IIF(IsNumeric(CALL_ANI)=1,CALL_ANI, CAST(0 AS bigint))) AS ANI
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 3, 2015 at 11:08 am
This worked:
IIF(IsNumeric(CALL_ANI)=1,CONVERT(bigint,CALL_ANI),0) AS ANI
Strange, no ?
September 3, 2015 at 11:24 am
Not really strange as it converts to an int within the IIF before being converted to a bigint.
I would try a different approach knowing the problems caused by ISNUMERIC[/url].
CREATE TABLE #TEST( CALL_ANI varchar(10))
INSERT INTO #TEST
VALUES
(''),
('1234'),
('7048884212'),
('ARE56465'),
('13656E2');
SELECT ISNULL(TRY_CONVERT(bigint, CALL_ANI),0) AS ANI
FROM #TEST
GO
DROP TABLE #TEST
September 3, 2015 at 1:02 pm
Luis Cazares (9/3/2015)
Not really strange as it converts to an int within the IIF before being converted to a bigint.the problems caused by ISNUMERIC
No excuse on Microsoft's part....should have been fixed long ago.
I saw a post on this site where someone created their own "IsBigInt" function.....
but there was way too much code and therefore overhead for me to want to use it.
Native functions are likely 10x faster than functions written in T-SQL.
September 3, 2015 at 1:14 pm
mar.ko (9/3/2015)
Luis Cazares (9/3/2015)
Not really strange as it converts to an int within the IIF before being converted to a bigint.the problems caused by ISNUMERIC
No excuse on Microsoft's part....should have been fixed long ago.
I saw a post on this site where someone created their own "IsBigInt" function.....
but there was way too much code and therefore overhead for me to want to use it.
Native functions are likely 10x faster than functions written in T-SQL.
Why should they fix it if it's not broken? It's not an ISINTEGER function, it just tells you if a value is a valid numeric for any of the datatypes available. The article that I linked on my previous post shows a simple validation to check if it's an all-digits string which most of the time would be able to fit a bigint.
September 3, 2015 at 1:32 pm
Here is that function that I had mentioned in a previous post.....ughhh
CREATE FUNCTION dbo.IsBigInt (@a varchar(30))
returns bit
AS
BEGIN
-- Submitted to SqlServerCentral by William Talada
DECLARE
@s-2 varchar(30),
@i int,
@IsNeg bit,
@valid int
-- assume the best
SET @valid = 1
SET @IsNeg=0
SET @s-2 = ltrim(rtrim(@a))
-- strip OFF negative sign
IF len(@s) > 0
AND LEFT(@s, 1) = '-'
BEGIN
SET @IsNeg=1
SET @s-2 = RIGHT(@s, len(@s) - 1)
END
-- strip OFF positive sign
IF len(@s) > 0
AND LEFT(@s, 1) = '+'
BEGIN
SET @s-2 = RIGHT(@a, len(@a) - 1)
END
-- strip leading zeros
while len(@s) > 1 and left(@s,1) = '0'
set @s-2 = right(@s, len(@s) - 1)
-- 19 digits max
IF len(@s) > 19 SET @valid = 0
-- the rest must be numbers only
SET @i = len(@s)
WHILE @i >= 1
BEGIN
IF charindex(substring(@s, @i, 1), '0123456789') = 0 SET @valid = 0
SET @i = @i - 1
END
-- check range
IF @valid = 1
AND len(@s) = 19
BEGIN
IF @isNeg = 1 AND @s-2 > '9223372036854775808' SET @valid = 0
IF @IsNeg = 0 AND @s-2 > '9223372036854775807' SET @valid = 0
END
RETURN @valid
END
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply