--=================分割存储过程======================
if exists(select name from sysobjects where name='wghp_fenge' and type='P')
drop procedure wghp_fenge
gocreate procedure wghp_fenge
@test varchar(255),
@m1 varchar(255) output,
@m2 varchar(255) output,
@m3 varchar(255) output,
@m4 varchar(255) output
as
declare @pos int
declare @cd int
declare @tm varchar(255)set @cd=len(@test)
select @pos=CHARINDEX('-',@test)
if(@pos>0)
begin
select @m1=substring(@test,1,@pos-1)
set @test=right(@test,@cd-@pos) set @cd=len(@test)
select @pos=CHARINDEX('-',@test)
if(@pos>1)
begin
select @m2=substring(@test,1,@pos-1)
set @test=right(@test,@cd-@pos) set @cd=len(@test)
select @pos=CHARINDEX('-',@test)
if(@pos>1)
begin
select @m3=substring(@test,1,@pos-1)
set @test=right(@test,@cd-@pos) set @cd=len(@test)
select @pos=CHARINDEX('-',@test)
if (@pos>1)
begin
select @m4=substring(@test,1,@pos-1)
set @test=right(@test,@cd-@pos)
end else
begin
set @m4=@test
end
end else
begin
set @m3=@test
set @m4=NULL
end
end else
begin
set @m2=@test
set @m3=NULL
Set @m4=NULL
end
end else
begin
set @m1=@test
set @m2=NULL
set @m3=NULL
set @m4=NULL
end
go
--=======================================
--===============================================================
----1.定义游标DECLARE dwdegl CURSOR FOR
select deh from ys_dwdegl ---*****
go---2.更新表
declare @t1 char(25)
declare @t2 char(25)
declare @t3 char(25)
declare @t4 char(25)declare @deh varchar(255)
declare @d char(25)close dwdegl
OPEN dwdegl
FETCH next from dwdegl INTO @dselect @deh=@d
exec wghp_fenge @test=@deh,@m1=@t1 output,@m2=@t2 output,@m3=@t3 output,@m4=@t4 outputselect @deh,@t1,@t2while @@FETCH_STATUS = 0
begin
set @t1=NULL
set @t2=NULL
set @t3=NULL
set @t4=NULL exec wghp_fenge @test=@deh,@m1=@t1 output,@m2=@t2 output,@m3=@t3 output,@m4=@t4 output update ys_dwdegl ---*******
set deh1=@t1,deh2=@t2,deh3=@t3,deh4=@t4
where deh=@deh FETCH dwdegl INTO @deh
end
---=3.关闭游标,删除游标
close dwdegl
DEALLOCATE dwdegl
goselect deh,deh1,deh2,deh3,deh4 from ys_
if exists(select name from sysobjects where name='wghp_fenge' and type='P')
drop procedure wghp_fenge
gocreate procedure wghp_fenge
@test varchar(255),
@m1 varchar(255) output,
@m2 varchar(255) output,
@m3 varchar(255) output,
@m4 varchar(255) output
as
declare @pos int
declare @cd int
declare @tm varchar(255)set @cd=len(@test)
select @pos=CHARINDEX('-',@test)
if(@pos>0)
begin
select @m1=substring(@test,1,@pos-1)
set @test=right(@test,@cd-@pos) set @cd=len(@test)
select @pos=CHARINDEX('-',@test)
if(@pos>1)
begin
select @m2=substring(@test,1,@pos-1)
set @test=right(@test,@cd-@pos) set @cd=len(@test)
select @pos=CHARINDEX('-',@test)
if(@pos>1)
begin
select @m3=substring(@test,1,@pos-1)
set @test=right(@test,@cd-@pos) set @cd=len(@test)
select @pos=CHARINDEX('-',@test)
if (@pos>1)
begin
select @m4=substring(@test,1,@pos-1)
set @test=right(@test,@cd-@pos)
end else
begin
set @m4=@test
end
end else
begin
set @m3=@test
set @m4=NULL
end
end else
begin
set @m2=@test
set @m3=NULL
Set @m4=NULL
end
end else
begin
set @m1=@test
set @m2=NULL
set @m3=NULL
set @m4=NULL
end
go
--=======================================
--===============================================================
----1.定义游标DECLARE dwdegl CURSOR FOR
select deh from ys_dwdegl ---*****
go---2.更新表
declare @t1 char(25)
declare @t2 char(25)
declare @t3 char(25)
declare @t4 char(25)declare @deh varchar(255)
declare @d char(25)close dwdegl
OPEN dwdegl
FETCH next from dwdegl INTO @dselect @deh=@d
exec wghp_fenge @test=@deh,@m1=@t1 output,@m2=@t2 output,@m3=@t3 output,@m4=@t4 outputselect @deh,@t1,@t2while @@FETCH_STATUS = 0
begin
set @t1=NULL
set @t2=NULL
set @t3=NULL
set @t4=NULL exec wghp_fenge @test=@deh,@m1=@t1 output,@m2=@t2 output,@m3=@t3 output,@m4=@t4 output update ys_dwdegl ---*******
set deh1=@t1,deh2=@t2,deh3=@t3,deh4=@t4
where deh=@deh FETCH dwdegl INTO @deh
end
---=3.关闭游标,删除游标
close dwdegl
DEALLOCATE dwdegl
goselect deh,deh1,deh2,deh3,deh4 from ys_
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货