在SQL中参数值超过8000(TEXT类型)的处理方法,以"逗号"分隔的字符串

create  function dbo.sms_fn_split_text(@text text)
returns @returntable table(subcolstr varchar(200))
as  
begin
        --------------------------定义变量-----------------------------------------------------------------------------
        declare @fieldlen bigint,@nbegin bigint,@nsubstrlen bigint,@nflag bigint,@flag int
 declare @sourcestring varchar(8000),@commastr varchar(8000),@splitstr varchar(8000),@splitlen int,@length int
        declare @branch_type varchar(10),@agent_branch_id varchar(50),@count bigint

        --------------------------赋初值-------------------------------------------------------------------------------
        select   @fieldlen =0,@nbegin=0,@nsubstrlen=0,@nflag = 0,@flag=0
        select @fieldlen = datalength(@text)
/*---------------------------------------------------------------------
        判断字段的段是否大于7000           
        如果大于7000则分多次放到varchar变量中进行分隔
----------------------------------------------------------------------*/       
        if @fieldlen >7000
        begin
                while @nbegin+1<=@fieldlen
                begin
                --------------------------------------------------------------------------------------------------
                        if @fieldlen-@nbegin>7000
                        begin
                        --------------------------------------------------------------------------------
                                set @nsubstrlen=7000
                                set @flag = 1
                        --------------------------------------------------------------------------------
                        end
                        else
                        begin
                        --------------------------------------------------------------------------------
                                set @nsubstrlen = @fieldlen - @nbegin   
                                set @flag =0
                        --------------------------------------------------------------------------------
                        end
                        select  @sourcestring=substring(@text,@nbegin+1,@nsubstrlen)
       
                        select  @splitstr = @sourcestring,
                                @splitlen = charindex(',',@splitstr),
                                @length   = datalength(@splitstr),
                                @nflag = @splitlen
                               
                       
                        while @length>0
                        begin
                        ----------------------------------------------------------------------------------
                         if     @splitlen=0
                                begin
                                --------------------------------------------------------------------------
                                        if @length = 7000
                                        begin
                                        ------------------------------------------------------------------
                                                delete @returntable
                                                insert @returntable (subcolstr)  values(null)
                                                goto end_label
                                        ------------------------------------------------------------------
                                        end
                                        if @flag=0
                                            set @nbegin = @nbegin + @length
                                        break
                                --------------------------------------------------------------------------
                                end
                                        set @nbegin = @nbegin + @nflag

                                 set    @commastr =@splitstr
                                        if @commastr <> ''
                                        insert @returntable (subcolstr) values(substring(@commastr,1,@splitlen-1))
                                 select @splitstr = substring(@commastr,@splitlen+1,@length),
                                               @length   = datalength(@splitstr),
                                               @splitlen = charindex(',',@splitstr)
                                        set    @nflag = @splitlen
                        -------------------------------------------------------------------------------------
                        end
                --------------------------------------------------------------------------------------------------------------
                end                
                set @splitlen=charindex(',',@splitstr) 
                if     @splitlen=0
                       set @splitlen=datalength(@splitstr)               

                if @splitstr<>''
                 insert @returntable (subcolstr) values(substring(@splitstr,1,@splitlen))
        end
/*---------------------------------------------------------------------
        如果小于7000
                则再判断是否为NULL值       
                        如果为NULL则在数据库中取相应的数据
                        否则放入到VARCHAR中进行分隔
----------------------------------------------------------------------*/  
        else
        begin                 
               select  @sourcestring=substring(@text,1,@fieldlen)
                -----------------------------------------------------------------------------------------------------
                select  @splitstr=@sourcestring,
                 @splitlen=charindex(',',@splitstr),
                 @length=datalength(@splitstr)
 
         while @length>0
         begin
         --------------------------------------------------------------------------------------
          if     @splitlen=0
                        set @splitlen=@length+1
        
          set    @commastr =@splitstr
          insert @returntable (subcolstr) values(substring(@commastr,1,@splitlen-1))
          select @splitstr = substring(@commastr,@splitlen+1,@length),
                        @length=datalength(@splitstr),
                        @splitlen=charindex(',',@splitstr)
                --------------------------------------------------------------------------------------
                end
        end       
        --------------------------------------------------------------------------------------------------------------
        end_label:   
        return
end
GO