自定义分割存储过程 SP_Split，将一个字符串分割成一个表

需要调用自定义函数 SF_Split    http://blog.csdn.net/keenweiwei/article/details/7090113

 

if  exists(select 1 from sysobjects where id=object_id('dbo.SP_Split') and xtype= 'P') 
begin
	drop PROCEDURE SP_Split
end
go

/*
	created on 02/11/2012,jonse
	调用例子如下： 
	
 
	SP_Split 'RA101118004!8!7,RA101118004!8!8,RA101118004!10!9',',','!'
	
	
执行结果为:

Short1        Short2      Short3
-----------  -------    --------- 
RA101118004   8           7
RA101118004   8           8
RA101118004   10          9


*/

create PROCEDURE SP_Split(@SourceSql varchar(8000),@StrSeprate varchar(1),@StrSeprate1 varchar(1))
 --实现split功能 的函数
as 
begin
	
    if object_id('tempdb.dbo.#tmp1') is not null
      drop table #tmp1  --删除临时表  
      
	select * into #tmp1 from dbo.[SF_Split](@SourceSql,@StrSeprate)
	 
	if exists(select 1 from #tmp1 where Short like '%'+@StrSeprate1+'%')
	begin
	    declare @str2 varchar(50)=(select top 1 short from #tmp1)
	    declare @iColumnCount int=(select (len(@str2)-len(replace(@str2,@StrSeprate1,'')))/len(@StrSeprate1))+1  
	    declare @sTableSql varchar(1000)='declare @temp2 table('
	    declare @sTableData varchar(max)=''
	    declare @iTmp int=1
	    
	    while(@iTmp<=@iColumnCount)
	    begin
			set @sTableSql+='Short' + CONVERT(varchar,@iTmp) +' varchar(50),'
			set @iTmp+=1
	    end
	    
	    select @sTableData +=' union all select '''+replace(Short,@StrSeprate1,''',''')+'''' from #tmp1 
	    
	    if(LEN(@sTableSql)>1)
	    begin
			set @sTableSql=SUBSTRING(@sTableSql,1,len(@sTableSql)-1) +') '
			set @sTableData=SUBSTRING(@sTableData,12,LEN(@sTableData)-11)
			EXEC(@sTableSql + ' insert into @temp2 '+ @sTableData + ' select * from @temp2')
			--print @sTableSql + ' insert into @temp2 '+ @sTableData + ' select * from @temp2'
		end
 
	end		
	  
end