CREATE function fun_get_parametevalue(@typesprc varchar(50),@parameter varchar(10))
returns numeric(18,6)
as
begin
declare @i int
declare @error int,@errorstr varchar(200)
declare @astr varchar(5000),@typestr varchar(2000),@typeno varchar(300),@typename varchar(400)
declare @pastr varchar(200),@ptypestr varchar(2000)
set @error=0
set @typeno=''
if(PATINDEX('%[吖-座]%',@typesprc)<>0 or charindex(@parameter,@typesprc collate Chinese_PRC_CS_AS_WS)=0 )
begin
return 1
end
set @i=1
set @typestr=@typesprc
set @astr=''
while @i<=6
begin
if charindex('-',@typestr)>0
begin
if @astr=''
set @astr=left(@typestr,charindex('-',@typestr)-1)
else
set @astr=@astr+'-'+left(@typestr,charindex('-',@typestr)-1)
set @typestr=right(@typestr,len(@typestr)-charindex('-',@typestr))
end
else
begin
if @astr=''
set @astr=@typestr
else
set @astr=@astr+'-'+@typestr
set @typestr=''
end
if exists(select 1 from sa_baojiatype with (nolock) where typeno collate Chinese_PRC_CS_AS_WS=@astr)
begin
select @typeno=typeno from sa_baojiatype with (nolock) where typeno collate Chinese_PRC_CS_AS_WS=@astr
set @pastr=@astr
set @ptypestr=@typestr
end
set @i=@i+1
end
if isnull(@typeno,'')<>''
begin
declare @id int
select @id=a.id from (
select ROW_NUMBER()over(order by a.autoid) as id,a.fieldname,isnull(a.kxx,0)as kxx ,a.autoid,ysleibie,isnull(a.xxys,0) as xxys,isnull(bjval,0)as bjval,isnull(bjmin,0)as bjmin
from sa_baojiatypelist4 a with (nolock)
inner join sa_baojiatype b with (nolock) on a.billno=b.billno
where b.typeno=@typeno and a.ysleibie>=0 and a.fieldname<>'代码'
) a
where a.fieldname collate Chinese_PRC_CS_AS_WS=@parameter
if(isnull(@id,'')<>'')
begin
set @typestr=@ptypestr
set @astr=@typestr
declare @sstr varchar(100),@dstr varchar(100)
select @sstr=thfield,@dstr=destfield from sa_baojiatype with (nolock) where typeno= @pastr
if ISNULL(@sstr,'')<>'' and ISNULL(@dstr,'')<>''
select @typestr=REPLACE(@typestr,@sstr,@dstr)
declare @jj int
set @jj=0
while @jj < @id
begin
if(charindex('-',@typestr)>0 )
begin
set @astr=left(@typestr,charindex('-',@typestr)-1)
set @typestr=right(@typestr,len(@typestr)-charindex('-',@typestr))
end
else
begin
set @astr=@typestr
set @typestr=''
end
set @jj=@jj+1
end
set @astr=ltrim(rtrim(@astr))
if charindex(@parameter,@astr)>0
begin
if dbo.StrComp(substring(@astr,1,len(@parameter)),@parameter)=1 and @parameter<>@astr
set @astr=substring(@astr,charindex(@parameter,@astr)+len(@parameter),len(@astr)-charindex(@parameter,@astr))
end
end
end
if (@error=0 and isnull(@astr,'')<>'' and PATINDEX('%[^0-9|.|-|+]%',@astr)=0)
return cast(@astr as float(20))
return 1
end
函数里面包含很多常用代码,适合新手
最新推荐文章于 2024-01-08 17:19:44 发布