急！！求解,存储过程两次插入相同的字符串该如何处理

Question

现在有一个参数 @path='hone/in/log,hone/in/date,home/in/relog'以逗号分隔的，把这个参数传入到存储过程中执行，第一次插入到表中是正确的
path
hone/in/log
hone/in/date
home/in/relog第二次插入没有从传入的@path='hone/in/log,hone/in/date,home/in/relog'中的第一个hone/in/log向数据库插，而是从最后home/in/relog循环插了三条到数据库，我是用了while charindex（',' , @path）
path
home/in/relog
home/in/relog
home/in/relog
我想达到的效果就是
path
hone/in/log
hone/in/date
home/in/relog
hone/in/log
hone/in/date
home/in/relog

Answer 1

/*按照符号分割字符串*/
create function [dbo].[m_split](@c varchar(2000),@split varchar(2))   
  returns @t table(col varchar(200))   
  as   
    begin   
      while(charindex(@split,@c)<>0)   
        begin   
          insert @t(col) values (substring(@c,1,charindex(@split,@c)-1))   
            set @c = stuff(@c,1,charindex(@split,@c),'') 
           -- SET @c = substring(@c,charindex(' ',@c)+1,len(@c))     
        end   
      insert @t(col) values (@c)   
      return   
end
 select * from dbo.m_split('hone/in/log,hone/in/date,home/in/relog',',')
/*结果
col
---------------
hone/in/log
hone/in/date
home/in/relog
*/

Answer 2

你没看明白我发的帖的意思吗，就是在第二次插入没有从传入的@path='hone/in/log,hone/in/date,home/in/relog'中的第一个hone/in/log向数据库插，而是从最后home/in/relog循环插了三条到数据库，我是用了while charindex（',' , @path）
path
home/in/relog
home/in/relog
home/in/relog
我想达到的效果就是
path
hone/in/log
hone/in/date
home/in/relog
hone/in/log
hone/in/date
home/in/relog

Answer 3

你不贴你的代码只说是用了 while charindex(',',@path)    这我们猜？

Answer 4

create procedure pro_insert_device 
( 
@module numeric(10,0), 
@postoffice     numeric(10,0), 
@general_ip varchar(50), 
@general_port   varchar(10), 
@general_path   varchar(1000) )as 
begin 
declare @path varchar(1000) 
declare @record_id int 
declare @file_serial_id int 
declare @seg_serial_id int 
declare @field_serial_id int 
declare @len int 
declare @i int select @path = @general_path if(@general_path is NULL) 
begin 
return 
end while charindex(',',@general_path)>0 
begin 
select @len = dataleength(@general_path) 
select @i = charindex(',',@general_path) 
select path = left(@general_path,@i-1) 
set @general_path = subString(@general_path,@i+1,len(@general_path)-@i) insert into device_node(field_serial_id,field_id,seg_serial_id,filed_value,filed_old_value,upgrade_flag) 
value(@field_serial_id,1,@seg_serial_id,@path,@path,@len-@i) end 
select @field_serial_id = @field_serial_id+1 
select @path = @general_path 
insert into device_node(field_serial_id,field_id,seg_serial_id,filed_value,filed_old_value,upgrade_flag) 
value(@field_serial_id,1,@seg_serial_id,@path,@path,1) 
while charindex(',',@general_path)>0 
begin 
select @len = dataleength(@general_path) 
select @i = charindex(',',@general_path) 
select path = left(@general_path,@i-1) 
set @general_path = subString(@general_path,@i+1,len(@general_path)-@i) insert into device_node(field_serial_id,field_id,seg_serial_id,filed_value,filed_old_value,upgrade_flag) 
value(@field_serial_id,3,@seg_serial_id,@path,@path,@len-@i) end 
select @field_serial_id = @field_serial_id+1 
select @path = @general_path 
insert into device_node(field_serial_id,field_id,seg_serial_id,filed_value,filed_old_value,upgrade_flag) 
value(@field_serial_id,3,@seg_serial_id,@path,@path,1) 
while charindex(',',@general_path)>0 
begin 
select @len = dataleength(@general_path) 
select @i = charindex(',',@general_path) 
select path = left(@general_path,@i-1) 
set @general_path = subString(@general_path,@i+1,len(@general_path)-@i) insert into device_node(field_serial_id,field_id,seg_serial_id,filed_value,filed_old_value,upgrade_flag) 
value(@field_serial_id,8,@seg_serial_id,@path,@path,@len-@i) end 
select @field_serial_id = @field_serial_id+1 
select @path = @general_path 
insert into device_node(field_serial_id,field_id,seg_serial_id,filed_value,filed_old_value,upgrade_flag) 
value(@field_serial_id,8,@seg_serial_id,@path,@path,1) 
while charindex(',',@general_path)>0 
begin 
select @len = dataleength(@general_path) 
select @i = charindex(',',@general_path) 
select path = left(@general_path,@i-1) 
set @general_path = subString(@general_path,@i+1,len(@general_path)-@i) insert into device_node(field_serial_id,field_id,seg_serial_id,filed_value,filed_old_value,upgrade_flag) 
value(@field_serial_id,9,@seg_serial_id,@path,@path,@len-@i) end 
select @field_serial_id = @field_serial_id+1 
select @path = @general_path 
insert into device_node(field_serial_id,field_id,seg_serial_id,filed_value,filed_old_value,upgrade_flag) 
value(@field_serial_id,9,@seg_serial_id,@path,@path,1) 
while charindex(',',@general_path)>0 
begin 
select @len = dataleength(@general_path) 
select @i = charindex(',',@general_path) 
select path = left(@general_path,@i-1) 
set @general_path = subString(@general_path,@i+1,len(@general_path)-@i) insert into device_node(field_serial_id,field_id,seg_serial_id,filed_value,filed_old_value,upgrade_flag) 
value(@field_serial_id,13,@seg_serial_id,@path,@path,@len-@i) end 
select @field_serial_id = @field_serial_id+1 
select @path = @general_path 
insert into device_node(field_serial_id,field_id,seg_serial_id,filed_value,filed_old_value,upgrade_flag) 
value(@field_serial_id,13,@seg_serial_id,@path,@path,1) 
while charindex(',',@general_path)>0 
begin 
select @len = dataleength(@general_path) 
select @i = charindex(',',@general_path) 
select path = left(@general_path,@i-1) 
set @general_path = subString(@general_path,@i+1,len(@general_path)-@i) insert into device_node(field_serial_id,field_id,seg_serial_id,filed_value,filed_old_value,upgrade_flag) 
value(@field_serial_id,14,@seg_serial_id,@path,@path,@len-@i) end 
select @field_serial_id = @field_serial_id+1 
select @path = @general_path 
insert into device_node(field_serial_id,field_id,seg_serial_id,filed_value,filed_old_value,upgrade_flag) 
value(@field_serial_id,14,@seg_serial_id,@path,@path,1) 
while charindex(',',@general_path)>0 
begin 
select @len = dataleength(@general_path) 
select @i = charindex(',',@general_path) 
select path = left(@general_path,@i-1) 
set @general_path = subString(@general_path,@i+1,len(@general_path)-@i) insert into device_node(field_serial_id,field_id,seg_serial_id,filed_value,filed_old_value,upgrade_flag) 
value(@field_serial_id,15,@seg_serial_id,@path,@path,@len-@i) end 
select @field_serial_id = @field_serial_id+1 
select @path = @general_path 
insert into device_node(field_serial_id,field_id,seg_serial_id,filed_value,filed_old_value,upgrade_flag) 
value(@field_serial_id,15,@seg_serial_id,@path,@path,1) end 
go 
execute pro_insert_device 192,100,'10.47.158.159','8433','/home/in/logs,/home/in/data,/home/in/redolog,/home/in/dict,/home/in/log' 
上面是存储过程，现在有一个参数 @path='/home/in/logs,/home/in/data,/home/in/redolog,/home/in/dict,/home/in/log' 
以逗号分隔的， 把这个参数传入到存储过程中执行，第一次插入到表中是正确的 
path 
/hone/in/logs 
/hone/in/data 
/home/in/relog 
/home/in/dict 
/home/in/log 
第二次插入没有从传入的@path='/home/in/logs,/home/in/data,/home/in/redolog,/home/in/dict,/home/in/log' 
中的第一个hone/in/logs向数据库插，而是从最后home/in/log循环插到数据库， 
path 
home/in/log 
home/in/log 
home/in/log 
home/in/log 
home/in/log 
home/in/log 
我想达到的效果就是 
path 
hone/in/logs 
hone/in/date 
home/in/relog 
home/in/dict 
hone/in/log hone/in/logs 
hone/in/date 
home/in/relog 
home/in/dict 
hone/in/log 

Answer 5

while charindex(',',@general_path)>0  
begin  
select @len = dataleength(@general_path)  
select @i = charindex(',',@general_path)  
select path = left(@general_path,@i-1)  
set @general_path = subString(@general_path,@i+1,len(@general_path)-@i)  insert into device_node(field_serial_id,field_id,seg_serial_id,filed_value,filed_old_value,upgrade_flag)  
value(@field_serial_id,1,@seg_serial_id,@path,@path,@len-@i)  end  
select @field_serial_id = @field_serial_id+1  
select @path = @general_path  
insert into device_node(field_serial_id,field_id,seg_serial_id,filed_value,filed_old_value,upgrade_flag)  
value(@field_serial_id,1,@seg_serial_id,@path,@path,1)  
while charindex(',',@general_path)>0  
begin  
select @len = dataleength(@general_path)  
select @i = charindex(',',@general_path)  
select path = left(@general_path,@i-1)  
set @general_path = subString(@general_path,@i+1,len(@general_path)-@i)  insert into device_node(field_serial_id,field_id,seg_serial_id,filed_value,filed_old_value,upgrade_flag)  
value(@field_serial_id,3,@seg_serial_id,@path,@path,@len-@i)  end  
select @field_serial_id = @field_serial_id+1  
select @path = @general_path  
insert into device_node(field_serial_id,field_id,seg_serial_id,filed_value,filed_old_value,upgrade_flag)  
value(@field_serial_id,3,@seg_serial_id,@path,@path,1)  红色部分和蓝色部分有什么不同，看来看去貌似是一样的啊！写一个循环判断就行了，怎么写这么多？

Answer 6

传的field_id不一样，field_id有1,3,8,9,13,14,15