SQL SERVERカーソルと文字列の分割
1302 ワード
Create Proc usp_splitStringaaaa (@tablename varchar(100) )
----@tablename ,aa,bb, ,
--40 0:100.00;1:300.00
--41 33:20.0
--42 0:100.00;1:200.00;4:500.00
as
begin
declare @zdxh varchar(50) ,@sql varchar(1000), @source varchar(100) ;
create Table #tempa(cs varchar(50));
create Table #splitTemp( zdxh varchar(50),mxxh varchar(50), je varchar(50));
set @sql = 'DECLARE MyCursor CURSOR dynamic FOR select * from '+@tablename ;
exec (@sql);
--DECLARE MyCursor CURSOR dynamic FOR select * from @tableanem;--T_AAAA;
OPEN MyCursor
FETCH NEXT FROM MyCursor INTO @zdxh,@source
WHILE @@FETCH_STATUS =0
BEGIN
set @sql='insert into #tempa select col='''+ replace(@source,';',''' union all select ''')+'''';
exec (@sql);
set @sql='insert into #splitTemp select '+@zdxh+',SUBSTRING(cs,1,CHARINDEX('':'',cs)-1) as mxxh,SUBSTRING(cs,CHARINDEX('':'',cs)+1,len(cs)- CHARINDEX('':'',cs) ) from #tempa';
exec(@sql)
FETCH NEXT FROM MyCursor INTO @zdxh,@source
END
CLOSE MyCursor
DEALLOCATE MyCursor
select * from #splitTemp;
drop table #tempa;
drop table #splitTemp;
end