存储过程问题

Question

CREATE DEFINER=`root`@`` PROCEDURE `TeamMember_InsertMembers`(
    _TeamId INT,
    _EmployeeIds VARCHAR(250)
)
BEGIN
    set @RowCount=0;
    set @SqlStr = 'SELECT length(replace(_EmployeeIds,",","")) INTO @RowCount';
    
    PREPARE StmtTotalCount FROM @SqlStr;
    EXECUTE StmtTotalCount;
    DEALLOCATE PREPARE StmtTotalCount;
    
    set @End = 1;
    set @EmployeeId = 0;
    while (@End < @RowCount+1) do
    IF @End = 1 then
        set @SqlStr = 'select SUBSTRING_INDEX(_EmployeeIds,",",@End) INTO @EmployeeId';
        PREPARE StmtTotalCount FROM @SqlStr;
        EXECUTE StmtTotalCount;
        DEALLOCATE PREPARE StmtTotalCount;
        
        INSERT INTO TeamMember
        (
            TeamId,
            EmployeeId
        )
        VALUES
        ( 
            _TeamId, 
            @EmployeeId
        );
    else
        set @SqlStr = 'select SUBSTRING_INDEX(SUBSTRING_INDEX(_EmployeeIds,",",@End),",",-1) INTO @EmployeeId';
        PREPARE StmtTotalCount FROM @SqlStr;
        EXECUTE StmtTotalCount;
        DEALLOCATE PREPARE StmtTotalCount;
        
        INSERT INTO TeamMember
        (
            TeamId,
            EmployeeId
        )
        VALUES
        ( 
            _TeamId, 
            @EmployeeId
        );
    END IF;
    set @End = @End + 1;
    end while;
END$$请问下这个存储过程执行失败，是哪里错了 应该怎么改？？

Answer 1

报这个错误 Code: 1054 Unknown column '_EmployeeIds' in 'field list'，表结构是这样的
biao(b_id,c_id) 2个都是主键 都不能为空 想用这个存储过程来循环插入多条记录。。
例如传入(1,'1,2,3,4,5,10')
他就可以做到
insert biao(b_id,c_id)values(1,'1'),
insert biao(b_id,c_id)values(1,'2'),
......

Answer 2

set @SqlStr = concat('select SUBSTRING_INDEX(',_EmployeeIds,',",",@End) INTO @EmployeeId');

Answer 3

 set @SqlStr = 'SELECT length(replace(_EmployeeIds,",","")) INTO @RowCount';
    
  PREPARE StmtTotalCount FROM @SqlStr;
  EXECUTE StmtTotalCount;
  DEALLOCATE PREPARE StmtTotalCount;
->set @RowCount = (SELECT length(replace(_EmployeeIds,',',''))) ;

Answer 4

你可以参考这个贴子中解决方法。
http://topic.csdn.net/u/20100921/11/f649d359-1fca-49ca-97f7-27231fb9c427.html

Answer 5

恩 我已经解决了 谢谢大家了 呵呵
我发现传入的值_EmployeeIds 在循环里无法访问，要用ACMAIN_CHM的方法或者另一个方法。
我是set @EmployeeIds=_EmployeeIds,这样就没问题了 循环里使用@EmployeeIds来执行

Answer 6

可以传参数
  set @SqlStr = 'select SUBSTRING_INDEX(_EmployeeIds,",",?) INTO ?';
  PREPARE StmtTotalCount FROM @SqlStr;
  EXECUTE StmtTotalCount using @end,@EmployeeId;