sql server 2005 分别用值函数和存储过程拆分字符串成列

Create PROCEDURE [dbo].[label_Add_Bat] 
@labels NVARCHAR(400) -- eg:N'asd,ad,asdoashdo,12313j1,asdasda,asdasd,'
AS
BEGIN

    DECLARE @temp TABLE (col NVARCHAR(100))    
    DECLARE @tlabel NVARCHAR(100) 

    INSERT INTO @temp SELECT col FROM split(@labels,N',') 

    declare   cur_temp   cursor   KEYSET   
    for   
    select   col   from  @temp 
      
    open   cur_temp   

    FETCH   NEXT   FROM   cur_temp   INTO   @tlabel  
     
    while (@@FETCH_STATUS=0)   
    begin   
         IF(EXISTS(SELECT label_Id FROM label WHERE label_Caption=@tlabel))
            BEGIN
                 UPDATE label SET label_Num = label_Num + 1 WHERE label_Caption=@tlabel
            END
         ELSE
             BEGIN
                 INSERT INTO [label] ([label_Caption]) VALUES (@tlabel)
             END
        FETCH   NEXT   FROM   cur_temp   INTO   @tlabel  
    end   
    
    close   cur_temp   
    
    DEALLOCATE   cur_temp   
END

 

表值函数split:

 

CREATE function [dbo].[split](@c varchar(max),@split varchar(2)) 
returns @t table(col varchar(20)) 
as 
BEGIN
    DECLARE @item NVARCHAR(20)
    
    --params
    while(charindex(@split,@c)>0) 
    begin 
        SET @item=substring(@c,1,charindex(@split,@c)-1)
        
        /*check is empty*/
        IF(RTRIM(LTRIM(@item))!='')
        BEGIN
            insert @t(col) values (@item)              
        END
        
        set @c = stuff(@c,1,charindex(@split,@c),'')        
    end 
    
    --one param    
    IF(RTRIM(LTRIM(@c))!='')
    BEGIN
        insert @t(col) values (@c) 
    END
    
    
    return 
end