sybase存储过程

[hygougou]

这句sqlserver存储过程的语句If IsNumeric(@brnltmp) = 1
如何转换成sybase的语句

[hobbylu]

create procedure sp__isnumber
@instr varchar(255), --string to test for numeric compatibility (scientific notation not supported, sorry)
@intype char(20)='any', --if given, validates whether input string can be converted to specified numeric type.
-- should be one of int/integer/smallint/tinyint/byte/dec(P[,S])/decimal(P[,S])/numeric(P[,S]),
-- defaults to "any" which validates as if specified type were decimal(38,38)
@valid char(1)=NULL output, --returned as 'Y' if conversion is possible, 'N' if conversion is not possible
@outldec decimal(38)=NULL output, --returned only when @intype in (I,S,T,B,D). Integer part of the number the string converts to
@outrdec decimal(38,38)=NULL output, --returned only when @intype = D Decimal part of the number the string converts to
@outerr varchar(50)=NULL output --error message - returned only when conversion is invalid
as begin
set nocount on
declare @sign char(1),
@type char(2),
@prec smallint,
@scale smallint,
@precstr varchar(20),
@scalestr varchar(20),
@lstr varchar(255), @rstr varchar(255),
@llen smallint, @rlen smallint

--default parameters(emtpy string and null), @instr->default to "NOTANUM", @intype->default to "any"
select @instr=isnull(nullif(@instr,''),'NOTANUM'), @intype=isnull(nullif(@intype,''),'any')
select @instr=ltrim(rtrim(@instr)), @intype=case when @intype='any' then 'decimal(38,38)' else ltrim(rtrim(@intype)) end

--check for valid type
select @type=case
when patindex('decimal(%)',@intype)>0 then 'D'
when patindex('dec(%)',@intype)>0 then 'D'
when patindex('numeric(%)',@intype)>0 then 'D'
when @intype='int' or @intype='integer' then 'I'
when @intype='smallint' then 'S'
when @intype='tinyint' or @intype='byte' then 'T'
end
if @type is NULL
begin
select @outerr='invalid type', @valid='N', @outldec=NULL, @outrdec=NULL
return 0
end

--parse out precision and scale for decimal/numeric types
if @type='D'
begin
select @precstr=substring(@intype,patindex('%(%',@intype) +1, patindex('%[,)]%',@intype)-patindex('%(%',@intype)-1)
if patindex('decimal(%,%)',@intype) > 0 or patindex('dec(%,%)',@intype) > 0 or patindex('numeric(%,%)', @intype) > 0
select @scalestr=substring(@intype,patindex('%,%',@intype )+1, patindex('%)%',@intype)-patindex('%,%',@intype)-1)
select @precstr=isnull(nullif(@precstr,''),'NOPREC'), @scalestr=isnull(nullif(@scalestr,''),'0')
if (@precstr='NOPREC' and patindex('%[^0-9]%',@precstr)>0) or
(@precstr='NOPREC' ) or
(patindex('%[^0-9]%',@scalestr)>0)
begin
select @outerr='invalid type', @valid='N', @outldec=NULL, @outrdec=NULL
return 0
end
select @prec=convert(smallint,@precstr), @scale=convert(smallint,@scalestr)
end

--The format passed, now check the number

--parse when leading plus sign or leading decimal or both, plus strip out decimal point
if patindex('[-+].%',@instr) > 0 -- (example: +.003 or -.003)
select @sign=substring(@instr,1,1),
@lstr='0',
@rstr=substring(@instr,3,999)
if patindex('.%',@instr) > 0 -- (example: .003)
select @sign='+',
@lstr='0',
@rstr=substring(@instr,2,999)
if patindex('[-+][^.]%',@instr) > 0 -- (example +100, -123
select @sign=substring(@instr,1,1),
@lstr=case
when patindex('%.%',@instr) > 0 then substring(@instr,2,patindex('%.%',@instr)-2)
else substring(@instr,2,999)
end,
@rstr=case
when patindex('%.%',@instr) > 0 then substring(@instr,patindex('%.%',@instr)+1,999)
else '0'
end
if patindex('[0-9]%',@instr) > 0 -- (example: 100 1.234 1000.)
select @sign='+',
@lstr=case
when patindex('%.%',@instr) > 0 then substring(@instr,1,patindex('%.%',@instr)-1)
else substring(@instr,1,999)
end,
@rstr=case
when patindex('%.%',@instr) > 0 then substring(@instr,patindex('%.%',@instr)+1,999)
else '0'
end
select @lstr=isnull(@lstr,'0'), @rstr=isnull(@rstr,'0')

--Allow only digits 0-9 in the remaining strings:
if patindex('%[^0-9]%',@lstr)>0 or patindex('%[^0-9]%',@rstr)>0
begin
select @outerr='invalid character in number', @valid='N', @outldec=NULL, @outrdec=NULL
return 0
end

--check length without converting yet
select @lstr=replicate('0',38-char_length(@lstr))+@lstr, @rstr=@rstr+replicate('0',38-char_length(@rstr))
select @llen=39-patindex('%[1-9]%',@lstr), @rlen=39-patindex('%[1-9]%',reverse(@rstr))
select @llen=case when @llen=39 then 0 else @llen end, @rlen=case when @rlen=39 then 0 else @rlen end
if (@type in ('I','S','T') and @rstr>'00000000000000000000000000000000000000') or -- no digits past decimal pt
(@type='I' and @sign = '-' and @lstr > '00000000000000000000000000002147483648') or -- less than min(int)
(@type='I' and @sign = '+' and @lstr > '00000000000000000000000000002147483647') or -- greater than max(int)
(@type='S' and @sign = '-' and @lstr > '00000000000000000000000000000000032768') or -- less than min(smallint)
(@type='S' and @sign = '+' and @lstr > '00000000000000000000000000000000032767') or -- greater than max(smallint)
(@type='T' and @lstr > '00000000000000000000000000000000000255') or -- greater than max(tinyint)
(@type='T' and @sign = '-' ) or -- smaller than min(tinyint)
(@type='D' and @llen+@rlen > @prec) or -- too many decimal digits
(@type='D' and @rlen > @scale) -- decimal scale too great
begin
select @outerr=
case
when (@type in ('I','S','T') and @rstr>'00000000000000000000000000000000000000') then 'digits past decimal in integer type'
when (@type='I' and @sign = '-' and @lstr > '00000000000000000000000000002147483648') then 'int value less than min(int)'
when (@type='I' and @sign = '+' and @lstr > '00000000000000000000000000002147483647') then 'int value greater than max(int)'
when (@type='S' and @sign = '-' and @lstr > '00000000000000000000000000000000032768') then 'smallint value less than min(smallint)'
when (@type='S' and @sign = '+' and @lstr > '00000000000000000000000000000000032767') then 'smallint value greater than max(smallint)'
when (@type='T' and @lstr > '00000000000000000000000000000000000255') then 'tinyint value greater than max(tinyint)'
when (@type='T' and @sign = '-' ) then 'tinyint value smaller than min(tinyint)'
when (@type='D' and @llen+@rlen > @prec) then 'too many decimal digits'
when (@type='D' and @rlen > @scale) then 'decimal scale too many digits'
end, @valid='N', @outldec=NULL, @outrdec=NULL
return 0
end

--All the checks passed- return a valid number:
select @outerr='valid', @valid='Y',
@outldec=convert(decimal(38),@lstr) * convert(smallint,@sign+'1'),
@outrdec=convert(decimal(38,38),'0.'+@rstr) * convert(smallint,@sign+'1')
return 0
end
你用这个存储过程，保证好使

[hygougou]

sybase是不是没有这样的内部函数？这也太长了吧，不知道速度怎么样？

[hygougou]

hobbylu(东方一剑），进来看看呀，你这个函数不行呀

[hygougou]

hobbylu(东方一剑），进来看看呀，你这个函数不行呀