存储过程里怎么循环插入多条记录？

Question

存储过程里怎么循环插入多条记录？
我的思路是这样，但是有错误，不知道怎么截断字符串，然后循环保存
CREATE PROCEDURE  test@DepartmentID nvarchar(500),
@DepartmentName nvarchar(500)
as
  set        @DepartmentID   ='1,2,3,4,5'   
  set        @DepartmentName   ='a,b,c,d,e'   
  
   while   @i<=@len   
    insert   into   test(strid,strname)    values(@str2,@str1)   
  set   @i=@i+1   
  end
test 表结构是
strid,strname
1       a
2       b
3       c
4       d
5       e

Answer 1

CREATE PROCEDURE  test 
@DepartmentID nvarchar(500), 
@DepartmentName nvarchar(500) 
as 
    --一下两句不是必须的，从逻辑上说，应该是调用存储过程时传入的参数
    --set @DepartmentID='1,2,3,4,5'       
    --set @DepartmentName='a,b,c,d,e'       
    
    set @len=100
    while @i<=@len   
    begin
        insert into test(strid,strname) values(@str2,@str1)       
        set  @i=@i+1       
    loop 

Answer 2

哦，漏了 @str2,@str1 两个变量，不上
CREATE PROCEDURE  test 
@DepartmentID nvarchar(500), 
@DepartmentName nvarchar(500) 
as 
    --一下两句不是必须的，从逻辑上说，应该是调用存储过程时传入的参数
    --set @DepartmentID='1,2,3,4,5'       
    --set @DepartmentName='a,b,c,d,e' 
      
    DECLARE @str1 varchar(100)
    DECLARE @str2 varchar(100)
    DECLARE @len int    set @len=100
    while @i<=@len   
    begin
        set @str1='abcdefg'
        set @str2='1234567'
        insert into test(strid,strname) values(@str2,@str1)       
        set  @i=@i+1       
    loop 

Answer 3

唉，还是错了，粗心大意啊把上面的 loop 改成 end

Answer 4

declare @DepartmentIDfirst varchar(10),@DepartmentNamefirst varchar(10)
set @DepartmentIDfirst=left(@DepartmentID,1)
set @DepartmentNamefirst=left(@DepartmentName,1)
insert into ....................
set DepartmentID=stuff(@DepartmentID,charindex(@DepartmentIDfirst,@DepartmentID),2,'') 
set DepartmentName= stuff(@DepartmentName,charindex(@DepartmentNamefirst,@DepartmentName),2,'') 循环下

Answer 5

create   PROCEDURE     test 
 @DepartmentID   nvarchar(500), 
 @DepartmentName   nvarchar(500) 
as
set nocount on
declare @str1 varchar(50),@str2 varchar(50)
declare @test table(strid varchar(50),strname varchar(50))while  charindex(',',@DepartmentID)>0
begin
set @str1=left(@DepartmentName,charindex(',',@DepartmentName)-1)
set @str2=left(@DepartmentID,charindex(',',@DepartmentID)-1)
set @DepartmentName=stuff(@DepartmentName,1,charindex(',',@DepartmentName),'')
set @DepartmentID=stuff(@DepartmentID,1,charindex(',',@DepartmentID),'')

        insert @test(strid,strname) values(@str2,@str1)       
end
if @DepartmentID<>''
insert @test(strid,strname) values(@DepartmentID,@DepartmentName)   
select * from @testgo
exec test '1,2,3,4,5' ,'a,b,c,d,e'
--result
/*strid                                              strname                                            
-------------------------------------------------- -------------------------------------------------- 
1                                                  a
2                                                  b
3                                                  c
4                                                  d
5                                                  e*/

Answer 6

create table test0(strid int,strname varchar(50))
CREATE   PROCEDURE     testp
@DepartmentID   nvarchar(500), 
@DepartmentName   nvarchar(500) 
as 
declare @str1   nvarchar(500), @str2   nvarchar(500) 
--前提@DepartmentID和@DepartmentName里面的数量是同样多的。
set    @DepartmentID  =@DepartmentID+','      
set    @DepartmentName =@DepartmentName+','       
while(charindex(',',@DepartmentID)>0)       
begin
set @str2=substring(@DepartmentID,1,charindex(',',@DepartmentID)-1)
set @DepartmentID=substring(@DepartmentID,charindex(',',@DepartmentID)+1,len(@DepartmentID))
set @str1=substring(@DepartmentName,1,charindex(',',@DepartmentName)-1)
set @DepartmentName=substring(@DepartmentName,charindex(',',@DepartmentName)+1,len(@DepartmentName))
insert  into  test0(strid,strname)  values(@str2,@str1)        
end 
exec testp '1,2,3,4,5','a,b,c,d,e'select * from test0

Answer 7

CREATE PROCEDURE  test 
@DepartmentID nvarchar(500), 
@DepartmentName nvarchar(500) 
as 
Begin    DECLARE @str1 varchar(100)
    DECLARE @str2 varchar(100)
    DECLARE @len int,@i int,@j int
set @i=0
set @len=0
set @j=1
    set @len=len(replace(@departmentid,',',''))    while (@i<=@len and @j<=(2*@len-1))  
    begin
        set @str1=substring(@departmentid,@j,1)
        set @str2=substring(@DepartmentName,@j,1)
        insert into test(strid,strname) values(@str1,@str2)       
        set @i=@i+1 
set @j=@j+2      
    endEnd

Answer 8

create table #test(strid varchar(10),strname varchar(10))alter PROCEDURE  #p_test 
@DepartmentID nvarchar(500), 
@DepartmentName nvarchar(500) 
as 
begin 
 declare @str1 varchar(10),@str2 varchar(10),@len int  
while charindex(',',@departmentid)>0 and charindex(',',@DepartmentName)>0 
begin
select @str1=left(@departmentid,charindex(',',@departmentid)-1),@str2=left(@DepartmentName,charindex(',',@DepartmentName)-1)
insert into #test (strid,strname) values(@str1,@str2)
select @departmentid=substring(@departmentid,charindex(',',@departmentid)+1,len(@departmentid)), @DepartmentName=substring(@DepartmentName,charindex(',',@DepartmentName)+1,len(@DepartmentName))

end      
select * from #test
endexec #p_test '1,2,3,4,5','a,b,c,d,e'strid      strname
---------- ----------
1          a
2          b
3          c
4          d