疑难杂症啊。存储过程里指定删除条件，却将我数据都删除掉啦

Question

create procedure p_AddArtisType(isAdd int,aId varchar(50),tIds varchar(500) )
BEGIN  
        if  isAdd=1 then
if tIds!='' then
set @updatesql =CONCAT(' insert into artiststype(AId,TId,IdPath,NamePath) select ',aId,',ID,FullID,FullName from t_part_info where ID in(',tIds,')');
                        PREPARE sqltext from @updatesql;
                        execute sqltext;
end if;
else 

if tIds!='' then
                       delete from  artiststype where AId=cast(aId as unsigned);
                       commit;
/*
set @updatesql =CONCAT('insert into artiststype(AId,TId,IdPath,NamePath) select ',aId,',ID,FullID,FullName from t_part_info where ID in(',tIds,');');
PREPARE sqltext from @updatesql; 
                 execute sqltext;
*/
end if;
end if;
END
执行 call p_AddArtisType(0,'10','15,16')
结果把所有数据都删除掉。
真被他搞死啦

Answer 1

注意变量名与字段名不要重名，
create procedure p_AddArtisType(isAdd int,aId1 varchar(50),tIds varchar(500) )
BEGIN  
        if  isAdd=1 then
        if tIds!='' then     
            set @updatesql =CONCAT(' insert into artiststype(AId,TId,IdPath,NamePath) select ',aId,',ID,FullID,FullName from t_part_info where ID in(',tIds,')');
                        PREPARE sqltext from @updatesql;
                        execute sqltext;
        end if;        
    else  
         
        if tIds!='' then
                       delete from  artiststype where AId=0+aId1;
                       commit;
/*
            set @updatesql =CONCAT('insert into artiststype(AId,TId,IdPath,NamePath) select ',aId,',ID,FullID,FullName from t_part_info where ID in(',tIds,');');
            PREPARE sqltext from @updatesql; 
                    execute sqltext;
*/
        end if;
    end if;
END