CREATE FUNCTION dbo.f_hex_dec(@s varchar(32))
RETURNS bigint
AS
BEGIN
DECLARE @i bigint,@result bigint
SELECT @i=0,@result=0,@s=RTRIM(LTRIM(UPPER(REVERSE(@s))))
WHILE @i<LEN(@s)
BEGIN
IF SUBSTRING(@s,@i+1,1) not between '0' and '9' and SUBSTRING(@s,@i+1,1) not between 'A' and 'F'
BEGIN
SELECT @result=0
break
END
SELECT @[email protected]+(CHARINDEX(SUBSTRING(@s,@i+1,1),'0123456789ABCDEF')-1)*POWER(16,@i),@[email protected]+1
END
RETURN @result
END
GO
调用函数运行：select oid from lists where oid=dbo.f_hex_dec('7a7a003500bc0001') and state=0 and tid=13出现错误为：类型 int 发生算术溢出错误，值 = 4294967296.000000。
为什么呢？RETURNS为bigint，@i bigint,@result bigint都为bigint，这些来存储16个16进制数应该没问题啊？请知道者帮忙修改下代码？谢谢了
------解决方案--------------------
修改后
- SQL code
create FUNCTION dbo.f_hex_dec(@s varchar(max)) RETURNS bigint AS BEGIN DECLARE @i BIGINT , @result BIGINT DECLARE @data BIGINT --添加了这个 SET @data =16 SELECT @i = 0 , @result = 0 , @s = RTRIM(LTRIM(UPPER(REVERSE(@s)))) WHILE @i < DATALENGTH(@s) BEGIN IF SUBSTRING(@s, @i + 1, 1) NOT BETWEEN '0' AND '9' AND SUBSTRING(@s, @i + 1, 1) NOT BETWEEN 'A' AND 'F' BEGIN SELECT @result = 0 BREAK END SELECT @result = @result + ( CHARINDEX(SUBSTRING(@s, @i + 1, 1), '0123456789ABCDEF') - 1 ) * POWER(@data, @i) , @i = @i + 1 END RETURN @result END SELECT dbo.f_hex_dec('7a7a003500bc0001')--8825366647431495681