首页 新闻 会员 周边 捐助

怎么接收字符串分割后的表

0
悬赏园豆:10 [待解决问题]
create function [dbo].[SplitString]
    (
        @Input nvarchar(max),
        @Separator nvarchar(max)=',', 
        @RemoveEmptyEntries bit=1 
    )
    returns @TABLE table 
    (
        [Id] int identity(1,1),
        [Value] nvarchar(max)
    ) 
    as
    begin 
        declare @Index int, @Entry nvarchar(max)
        set @Index = charindex(@Separator,@Input)

        while (@Index>0)
        begin
            set @Entry=ltrim(rtrim(substring(@Input, 1, @Index-1)))
            
            if (@RemoveEmptyEntries=0) or (@RemoveEmptyEntries=1 and @Entry<>'')
                begin
                    insert into @TABLE([Value]) Values(@Entry)
                end


            set @Input = substring(@Input, @Index+datalength(@Separator)/2, len(@Input))
            set @Index = charindex(@Separator, @Input)
        end
        
        set @Entry=ltrim(rtrim(@Input))
        if (@RemoveEmptyEntries=0) or (@RemoveEmptyEntries=1 and @Entry<>'')
            begin
                insert into @TABLE([Value]) Values(@Entry)
            end

        return
    end

上面是分割字符串的方法,下面是使用的存储过程

CREATE proc [dbo].[P_2Z2]
     @LotteryNumber varchar(50),
     @CheckNumber varchar(50),
     @Pos varchar(50),
     @num int output
      as 
      set @num=0
        declare @num1 varchar(20)
        declare @num2 varchar(20)
        if @Pos='L'
        begin
         set @num1=( select [value] from [dbo].[SplitString](@LotteryNumber, ',',0) where id=1)
         set @num2= (select [value] from [dbo].[SplitString](@LotteryNumber, ',',0) where id=2)
         set @LotteryNumber=@num1+','+@num2
        end
        declare @levelv int
        set @levelv=(select COUNT( [value]) from [dbo].[SplitString](@LotteryNumber, ',',0))
        if  @levelv=3 and @Pos='R'
        begin
         set @num1= (select [value] from [dbo].[SplitString](@LotteryNumber, ',',0) where id=2)
         set @num2= (select [value] from [dbo].[SplitString](@LotteryNumber, ',',0) where id=3)
         set @LotteryNumber=@num1+','+@num2
        end
      if @Pos='R'
        begin
         set @num1=( select [value] from [dbo].[SplitString](@LotteryNumber, ',',0) where id=3)
         set @num2= (select [value] from [dbo].[SplitString](@LotteryNumber, ',',0) where id=4)
         set @LotteryNumber=@num1+','+@num2
        end
         declare @t2value1 varchar(20)=(select [value] from  [dbo].[SplitString](@LotteryNumber, ',',0) where id=1)
         declare @t2value2 varchar(20)=(select [value] from  [dbo].[SplitString](@LotteryNumber, ',',0) where id=2)
           
        if  @t2value1 != @t2value2
        begin
        declare @s int
        set @s = 1
        declare @levelm int
        set @levelm = (select COUNT(*) from  [dbo].[SplitString](@CheckNumber, '#',0))
        while @s<= @levelm
        begin
         
         declare @value1 varchar(20)=(select [value] from  [dbo].[SplitString](@CheckNumber, '#',0) where id=@s)
         declare @t1value1 varchar(20)=(select [value] from  [dbo].[SplitString](@value1, ',',0) where id=1)
         declare @t1value2 varchar(20)=(select [value] from  [dbo].[SplitString](@value1, ',',0) where id=2)
          
           if charindex(@t2value1,@t1value1)!=0 and charindex(@t2value2,@t1value2)!=0
           begin
          set @num=@num+1
           end
           
        set  @s=@s+1
        end       
        end


其中存在大量这样的代码

  set @num1=( select [value] from [dbo].[SplitString](@LotteryNumber, ',',0) where id=1)
         set @num2= (select [value] from [dbo].[SplitString](@LotteryNumber, ',',0) where id=2)

这极其不简便,怎么样才能更好?在存储过程里接收分割字符串方法返回的表

 

 

下面是上面代码的方法

public static int P_2Z2(string LotteryNumber, string CheckNumber, string Pos)
        {
            int num = 0;
            string[] strArray = LotteryNumber.Split(new[] { ',' });
            if (Pos == "L")
            {
                LotteryNumber = strArray[0] + "," + strArray[1];
            }
            if (LotteryNumber.Split(new[] { ',' }).Length == 3)
            {
                if (Pos == "R")
                {
                    LotteryNumber = strArray[1] + "," + strArray[2];
                }
            }
            else if (Pos == "R")
            {
                LotteryNumber = strArray[3] + "," + strArray[4];
            }
            string[] strArray2 = LotteryNumber.Split(new[] { ',' });
            string[] strArray3 = CheckNumber.Split(new[] { '#' });
            if (strArray2[0] != strArray2[1])
            {
                for (int i = 0; i < strArray3.Length; i++)
                {
                    if ((strArray3[i].IndexOf(strArray2[0]) != -1) && (strArray3[i].IndexOf(strArray2[1]) != -1))
                    {
                        num++;
                    }
                }
            }
            return num;
        }

 

低调的小磁铁的主页 低调的小磁铁 | 初学一级 | 园豆:192
提问于:2013-11-19 16:31
< >
分享
所有回答(2)
0

定义一个临时表变量,把函数的返回值插入到临时表变量中,最后返回表中所有数据,再到程序中 拼接或分割

Zery | 园豆:6151 (大侠五级) | 2013-11-19 17:49

在临时表中怎么声明临时表变量,怎么使用 ?(你确定不是声明的临时表)

支持(0) 反对(0) 低调的小磁铁 | 园豆:192 (初学一级) | 2013-11-20 11:38

@低调的小磁铁: 

你的存储过程要做的事就是调用一个函数分割,然后储过程返回分割后的字符,

定义表变量,与定义变量一样的,往里插入数据 insert into @tableTemp select [value] from [dbo].[SplitString](@LotteryNumber, ',',0) where id=1

支持(0) 反对(0) Zery | 园豆:6151 (大侠五级) | 2013-11-20 12:06
0

你返回的是个表,那么可以采用临时表存储,也可以采用表变量。

 

select * into #临时表 from [dbo].[SplitString](@LotteryNumber, ',',0)

幻天芒 | 园豆:37205 (高人七级) | 2013-11-20 13:04
清除回答草稿
   您需要登录以后才能回答,未注册用户请先注册